Retrieving Lowest Level Hierarchy Members and Leaves in MDX

The Original Answer

I was answering questions on Stack Overflow when I came across a question about getting the last level of a hierarchy in MDX when you don’t know how many levels there will be. SSAS multidimensional allows for parent-child hierarchies without having to define a maximum number of levels.  A common use case for this is an employee reporting structure. You can create the same type of functionality is SSAS Tabular, but you have to build a flattened out structure and relate each level through DAX so there will always be a set number of levels even if they aren’t all populated.

This question was fun to answer because it can be done fairly simply and elegantly with MDX functions. I can get the members of the lowest level of the hierarchy with this:

[Dimension].[Hierarchy].LEVELS([Dimension].[Hierarchy].Levels.Count-1)

The Levels function can take a level number or name as the argument. [Hierarchy_expression].levels.count gives me the number of levels. I have to subtract one from that number when I supply that to the Levels function because it uses zero-based positioning (i.e. the 6th level is level 5).

I can accomplish the same thing using the Descendants function.

DESCENDANTS([Dimension].[Hierarchy].[All], ([Dimension].[Hierarchy].Levels.Count - 1), SELF)

I am giving the Descdendants function the distance as my second argument. I use the All member for the member expression so any descendant in the hierarchy could be returned.  Then you’ll see the same expression that I used above.  This time, I am subtracting one because this number represents the distance (number of levels) from the specified member. The description flag allows me to specify which descendants should be returned. It is optional and defaults to SELF if you leave it out, but I included it just to be explicit. It means that I am only returning the descendants on the level I specified.

The More Probable Answer

Once I had my answer all written up, I realized that I was answering the question that was asked, but I wasn’t sure that is what they really needed.  If you have a parent-child hierarchy, the bottom level might not mean anything in particular.  If the hierarchy is ragged, you can have leaves at various levels, so there would have to be a very interesting and specific question asked that would use the bottom level members as the answer.  I decided it was just as likely that the question might really be asking for all of the members that have no children (the leaves). I can use the Descendants function to answer this question as well.

DESCENDANTS([Dimension].[Hierarchy].[All], , LEAVES)

I once again supply my hierarchy expression.  I leave the level out and feed it a empty value and specify the description flag is LEAVES. This assumes that the cube is built with the HIDEMEMBERIF property set so that members are not repeated as children of themselves and there are no blank members.

Transitioning from thinking in T-SQL to thinking in MDX can be difficult, and sometimes the original poster doesn’t quite understand the way MDX works.  As I answer questions on Stack Overflow, I try to ask clarifying questions to get a better understanding of the reason for the question.  Many times someone will ask how to build a specific calculation, and other members will work with them to understand why they want to do it in order to provide a better answer.  I do this a lot at client sites as well.  I can save information workers lots of time by asking them what their end goal is rather than simply delivering an intermediate step when building their BI solution, providing them a complete and sometimes more accurate answer to their common analytical questions.