Display matrix of data using PATH function without step duplication when drilling

[ctaylor]

Hi,
Using data from an asset management software, I have been asked to display financial data in an exacting manor compared to the source software.  In the source, we have account trees and these trees take the same financial data and display them in different orders.

I took the ledger data and created a duplicate table and by using the PATH function I am able to recreate the hierarchy without issue to the breakdown of information. I then find the hierarchy level of each line item and the max depth in this set of data is 8. So, I created 8 columns (Levels 1-8) that contain the name in the path relevant to the level.

These levels are then dropped into the Matrix visual and viola, the data outputs exactly as the source displays it.

Please refer to images:

Matrix Visual   Visual Levels     Data table  

Heres the problem.

Up until level 4 each item has another level below it.  At Level 5 duplications begin because there is no path left to take for some items and this is what happens in the matrix.

Dupes begin

I came across this: https://community.powerbi.com/t5/Community-Blog/Financial-Reporting-Tips-For-Power-BI/ba-p/533580

specifically video 2 where he creates the hierarchy in a different way.  I tried this but it doesn't work for me because of the depth of the hierarchy.

What can I do to find a way to stop the duplication as seen in the last image where new items stop at level 5 but open 3 more times with the same data to get to level 8?  What I want to acheive is the matrix to stop offering expansion once the path reaches it's natural end instead of going all the way out to level 8.

Before you ask, this is a part of a huge project that makes it near impossible to share a pbix file because of dataflow, interconnectedness of the model, and sensitive data.  I can share code samples and some scrubbed table data if needed.

[amitchandak]

@ctaylor , I did not get it. Refer if this can help

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

[ctaylor]

@amitchandak 

That link just gives further insight into what I am already doing.  I am already creating the path, then chunking out the individual levels.

The problem is this....

Let's say a line item "Fake $" has a hierarchy level of 6.  It has six rollup items above it.  There is no 7th and no 8th level.  If I expand the matrix all the way out to the 8th level, there will be 3 cascading line items that say "Fake $", because the example I got this idea from didn't want to leave blanks for whatever reason.

Level 2 = IF('GL Nodes Community'[Hierarchy Depth] >=2, LOOKUPVALUE('GL Nodes Community'[NameKey], 'GL Nodes Community'[NodeKey], PATHITEM('GL Nodes Community'[Hierarchy Path], 2, INTEGER)), 'GL Nodes Community'[Level 1])

If I use what's in the link you posted, then I get "Fake $" in Level 6, then blanks in 7 and 8.

I need it to stop expanding at Level 6 for the item "Fake $" because there is no more new nodes to jump to after that point.

Do I need a measure or something set to 1 or 0 to somehow display the items up to their max depth?

[ctaylor]

I found a solution that gets me 99% to where I want to go. 

https://www.sqlbi.com/blog/alberto/2011/07/19/parent-child-hierarchies-in-tabular-with-denali/

I built the browsedepth and nodedepth measures and then incorporated them into my larger measures to calculate the amounts to blank out then hide columns that exceed the browsedepth.

The only thing that still happens is there are +/- icons at the lowest level of data if it happens before Level 8.

Any thoughts on how to fix this final visual issue?

[v-diye-msft]

Hi,

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file.