With a “ragged” hierarchy we often need a way to make sure that the hierarchy is displayed in a user friendly fashion when measures can be linked to any level (including parents).
There are two established solutions
1) The Pure DAX approach
Alberto was one of the first to publish a DAX workaround for the problem of ragged hierarchies here:
This workaround starts to get very complex when you have multiple hierarchies and performance can suffer on large hierarchies as the evaluation is at run time, but it does work and is “supported”.
2) HideMemberIf
BIDS helper exposes an “unsupported” technique to add HideMemberIf into the XMLA of the model definition. https://bidshelper.codeplex.com/
This solution looks more appealing as a lot of the work is done during ProcessRecalc when it does Hierarchy Processing – hopefully giving benefit at query time. It also involves less calculated measures, columns and code which sounds nice.
However two words of warning:
a) There is no guarantee that this will work in later editions
b) There are stability issues with a large number of levels. On the model we are working the ProcessRecalc and visual studio will just hang when we try to set this property on a NINE level hierarchy.
In the end we did use HideMemberIf, but only set the property on specific levels which we know can have measures attached to them to avoid the stability issues of having this turned on for all levels.
