MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Detecting members on the same branch

So far, we've covered cases when there is a need to isolate a single member in the hierarchy, whether it is a root member or any other member in the hierarchy. This recipe deals with detecting the ascendants or descendants of a member in a hierarchy. As a matter of fact, the detection does not need to go all the way to the top level or to the leaf level; it can detect only a certain cascading branch in a hierarchy.

Multilevels are often found in user hierarchies. For a certain member, we might need to apply certain calculations to its ascendants only, or its descendants only, or to only part of a cascading branch in the hierarchy. Let's illustrate this with a couple of examples.

Suppose we want to analyze dates. ...