Ragged Hierarchy in a Matrix

[ljmgordon]

Hi, folks,

I'm trying to build a dashboard to show data quality results per node in our Information Architecture Framework. My issue is: our hierarchy is ragged, some nodes are at L4, some at L6. I've searched all the help on here, and tried all sorts, but I still see one level lower than I want to in the Matrix. I'm trying to attach my .pbix file but my work laptop has too many security features, will keep trying.

I have a table that is the Content Classification, it looks like this:

Content Short Name	Parent
Time Off Reason	Time & Attendance
Time Bank	Time & Attendance
Time In/Time Out	Time & Attendance
Work Shift	Workforce Management
Scheduling Type	Workforce Management
Rotation Pattern	Workforce Management
Workforce Management	Position & Workforce Management
Position & Workforce Management	People
People	Information Architecture Framework

In this table I've added columns and measures for:

IAFPath = PATH ( [Content short name], [Parent] )

IAFNodeDepth = PATHLENGTH ( [IAFPath] )

IAFRowDepth = MAX ( [IAFNodeDepth] )

IAFBrowseDepth = ISINSCOPE ( [IAF L1] ) + ISINSCOPE ( [IAF L2]) + ... + ISINSCOPE ( [IAF L6] )

Then in my 'Rules' I have a measure to count rows (later I'll add in more data to show actual quality % results, just trying to build a base for now), and then in my Matrix I have rows as my IAF Hierarchy, and my Values as

Total Rules = 
VAR Val = [Count]
VAR IAFShowRow = 
[IAFBrowseDepth] <= [IAFRowDepth]
VAR Result = IF (IAFShowRow, Val)
RETURN
Result

BUT when I browse down my hierarchy, I get one level lower than the lowest level in my tree. Do I just have to live with this, or is there a way I can stop that bottom most level expanding?

[MFelix]

Hi @ljmgordon ,

Not sure if you followed this tutorial but this shows the way for the blank rows not to appear.

https://www.daxpatterns.com/parent-child-hierarchies/

[ljmgordon · Accepted solution]

Yup, that's the exact page I used to build my measures, but I still have this lowest level blank like you see in the screenshot. 

However, I did just manage to solve it by changing the "Total Rules" measure by making it 

[IAFBrowseDepth] < [IAFRowDepth]

 And now I do still actually see a + next to the bottom level, but there's no new row when I click it. Hurrah!

[MFelix]

Hi @ljmgordon ,

The + will not disappear since for all purposes there is still a level below that one, on some of the specific rows theres is no values then it won't show anyting, but for the rest it will present correct values.

Don't forget to mark the correct answer to help others.