SQL Centre of Excellence

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.

image

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)
image

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!

image

blog comments powered by Disqus

Page List

Page List