While the Matrix Control does has some new features for automatically handling ragged hierarchies as described by Reza Rad and others (https://radacad.com/removing-blanks-from-organizational-ragged-hierarchy-in-power-bi-matrix-visual), it falls short of what we want to achieve on most of my projects and still doesn’t come up to where MOLAP was with its automatic support via HideMemberIf
This is the approach that I use, and I was pleased to see that DAX patterns second edition also has this type of design pattern.
https://www.daxpatterns.com/parent-child-hierarchies/#
As an example we can take the “DimAccount” parent child hierarchy from the AdventureworksDW database and render it in PowerBI. on first cut it comes out as below.
The challenge is that we need to be able to determine when the current scope of a node is below the maximum depth.
To eliminate these blanks in DAX, there are a few approaches, but I’ll cover off using ISINSCOPE. The approach is as below
1) I assume that there is a PATH column in order to render the hierarchy like below
AccountPath = PATH ( Account[AccountKey], Account[ParentAccountKey] )
2) We add a calculated column to determine the Depth of the Node. as below
Account Depth = PATHLENGTH(Account[AccountPath] )
3) we add a calculated measure to determine the scope of the current node that is being navigated to. In this case the hierarchy can be between 2 and 6 levels deep.
AccountScope = switch(TRUE(), ISINSCOPE( Account[Account L6]), 6, ISINSCOPE( Account[Account L5]), 5, ISINSCOPE( Account[Account L4]), 4, ISINSCOPE( Account[Account L3]), 3, ISINSCOPE(Account[Account L2]),2 )
When that is in play we can see that the current scope of selected nodes is below the maximum depth of a hierarchy (Example below)
So the final step is to create a measure that returns 1 or null depending on if the level is in scope and multiply the Report measures by this. as below
HideAccount = if(Account[AccountScope] <= min(Account[Account Depth]) ,1)
and GL Amount = CALCULATE(sum(‘General Ledger'[BaseAmount] ) * Account[HideAccount])
You loose a bit of DAX performance using a “helper” measure rather than “inlining” the IF, but I do find this approach easier to support, so the choice is yours.
Once we put this into play, we can now get the desired result of a ragged hierarchy with no blanks!
This was SUPER helpful, thank you. I tried about 6 different other article methods before trying this one and this did the trick.
what if the middle level contains blank range? how to hide the middle level hierachy?
How can I modify this to allow rows to show up under the reports to hierarchy. Example for timesheets we are using the hierarchy 1-9 then underneath I need to show Posted/Unposted breakout.
I tried this approach with my ragged hierachy Matrix. It works except for one thing. It still defaults to a “+” for the line items that have blank lower levels. Is their something I am missing?
Thanks
You’ll always get the “+” if the level is not the leaf one.
we usually disable the + or – as a Statement like P&L is often fixed and not analytical (eg expand and collapse)