Leaf members of a level-less hierarchy

SSAS and TM1 cubes can have unbalanced parent-child (recursive) hierarchies which end up not having any real levels as we are used to see in normal hierarchies. Plus their lowest level of detail (leaf members) could be on any level within the hierarchy...

Problem

No level references and referencing a level is darn useful when making reports. 

One example of an issue related to having no levels appears when we try to show all the leaf members in our report. We just don't know where the leaf member might be ending up...

The official workaround in IBM website suggests to filter all members and pick just those that have no children. Something like this:

filter(

[Cube].[Dimension].[Hierarchy],

  count([Cube].[Measures].[Value] within set

                      children(currentMember([Cube].[Dimension].[Hierarchy])))

  = 0)

However, this just gives all leaf members. What if we need to find leaf members of just one part of this hierarchy, which the user chooses in their prompt page?

Solution

This is what I made for dealing with such a request for a level-less hierarchy:

filter(

                      filter([Cube].[Dimension].[Hierarchy],

                                            ancestor(currentMember([Cube].[Dimension].[Hierarchy]),

                                            level([Selected Member])) = [Selected Member] )

,

  count([Cube].[Measures].[Value] within set

                      children(currentMember([Cube].[Dimension].[Hierarchy])))

  = 0)

[Selected Member] is a member from this hierarchy selected in the prompt page.

The inner filter returns all members of the hierarchy which have the [Selected Member] as their parent. Then we can use this narrowed set to find just the leaf members in it. That we do with the help of the outer filter expression which is the solution provided by IBM.

Jep, sometimes we do miss the MDX capabilities of SSAS where this code would be so much shorter... :)