Forum Discussion
Helper IIHelp Needed with Visualizing Managerial Headcount Hierarchy in Matrix – Handling Variable Depths
🔗 https://www.sqlbi.com/articles/managing-hierarchical-organizations-in-power-bi-security-roles/
Challenge:
The depth of the manager path varies – some paths have 3 levels, others go up to 6. This results in "null" levels appearing in the matrix for shorter paths, which makes the visual cluttered and confusing.
Question:
How can I hide or avoid showing these null levels in the matrix visual? I want the matrix to dynamically adjust based on the actual depth of each manager's hierarchy and avoid confusion with the "null" rows.
I'll attach a screenshot to illustrate the problematic situation, I've taken it from the sample pbix.file related to the article above so it's easy to get the file.
-katina
Hi katina,
Thank you for your follow up.
Please find attached a screenshot and a revised sample PBIX file that may help resolve the issue:
We hope the information provided is useful. If you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
14 Replies
- Shahid12523
Community Champion
Unpivot your hierarchy columns (Level1 to Level6) in Power Query so they become rows instead of columns.
👉 Filter out nulls so only actual manager names show in the matrix.
👉 This makes the matrix dynamically adjust to each manager’s depth—no more clutter from blank levels.
katinaThis sounds clever, I'll try it right now and comment then. Thanks!
- katina
Unfortunately Anonymous , this approach almost works. However, now I have also the department manager and that person's manager (CEO) being calculated to the headcount with all their subordinates.
Do you have idea, what and how to modify? I'll create a dummy illustration, one moment...
- katina
Okay, here is a simple illustration what happens in the matrix. If this helps to solve my question govind_021 Shahid12523 Nabha-Ahmed
- Nabha-Ahmed
Super User
The null levels just mean that no employees exist under that branch — you can filter them out using ISBLANK() or PATHLENGTH() so only real departments show up.
- Nabha-Ahmed
AND,
Here’s a helpful video that walks through how to **remove blank levels in ragged hierarchies**—using functions like **ISINSCOPE**, **PATHLENGTH**, and DAX logic to dynamically hide empty levels:
[DAX Fridays! #112: Remove blanks from hierarchies with ISINSCOPE](https://www.youtube.com/watch?v=YEuxONjCl1A&utm_source=chatgpt.com)
Also, here’s a Microsoft Community article that offers a formula-based method to hide blank hierarchy values using a measure and visual-level filters:
You can use DAX like:```DAX
Measure =
IF (
HASONEVALUE ( Table[HierLevel] )
&& MAX ( Table[HierLevel] ) <> BLANK (),
SUM ( Table[Value] ),
CALCULATE ( SUM ( Table[Value] ), ALL ( Table[HierLevel] ) )
)
```This replaces blank levels with actual values only when they exist.
([community.fabric.microsoft.com][1])If either of these helps you clean up the matrix, let me know—or mark it as a solution!
[1]: https://community.fabric.microsoft.com/t5/Desktop/Hide-Blanks-Values-in-Matrix-Hierarchy/m-p/648253?utm_source=chatgpt.com "Hide Blanks Values in Matrix - Hierarchy"
v-pnaroju-msftCommunity Support
Thankyou, govind_021, Shahid12523, and Nabha-Ahmed, for your responses.
Hi katina,We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.
Based on my understanding of the issue, please find attached a screenshot and a sample PBIX file that may help resolve the problem:
We hope the information provided is helpful. If you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
- katina
Hi v-pnaroju-msft
Thank you so much for the pbix you provided! I tested it, and it almost works...
However, when I modify your matrix and add departments to the rows, the blank rows are still visible in the ragged hierarchy:Could you modify the measure, please? It would help a lot!
-katina
- v-pnaroju-msft
Hi katina,
Thank you for your follow up.
Please find attached a screenshot and a revised sample PBIX file that may help resolve the issue:
We hope the information provided is useful. If you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
- katina
Thank you so much, this solves my challenges! 😊
- katina
- govind_021
Hey , katina
Try usingSELECTEDVALUE(Level1)= BLANK(), then display blank values from the columns (drag area). Just don’t enable ‘Show items with no data’ — it might work.”
Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalyst- katina
Thanks govind_021 ! Please give me a bit more advise 🙂, where shall I put this SELECTEDVALUE(Level1) = BLANK() ? Do I create a measure and place it to a filter panel? How shall I tackle the rest levels which may be blank - do I need the same for each Level?
Sorry, I'm not (yet) familiar with the SELECTEDVALUE 🙂
- Nabha-Ahmed
This usually happens when the hierarchy has a fixed depth, so shorter manager paths show null values. A couple of options you can try:
Instead of expanding all hierarchy levels, create a calculated column or measure that only shows non-blank levels. For example, you can use ISBLANK() or IF( NOT ISBLANK([Manager]), [Manager]) logic to hide nulls.
Another option is to unpivot the hierarchy columns into a normalized table, so the matrix only expands to the actual number of levels that exist for each manager.
Alternatively, you could create a custom hierarchy using Parent–Child DAX functions (PATH(), PATHITEM()) and only display levels up to PATHLENGTH() for that manager.
This way the matrix dynamically adjusts and avoids showing extra null rows.
Hope this helps! If it solves your issue, please mark it as the solution !!
- katina
Thanks Nabha-Ahmed for your suggestions!
How do I apply the parent-child DAX to the visual? As I create a calculated column to my organization table - where shall I use for example the PATHLENGTH() column? I feel a bit lost as I don't figure out where shall I place these custom measures / columns 😶
