Help Needed with Visualizing Managerial Headcount Hierarchy in Matrix – Handling Variable Depths

[katina]

Hi everyone,

I'm working on a Power BI report where I need to visualize headcounts per department and per manager. The goal is to use a matrix to show active employees under each manager, summarized across all levels of the hierarchy. I have the headcount measure working properly, and I've also built the organizational hierarchy for row-level security using the method described here:
🔗 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.

Thanks in advance for any guidance or suggestions!
-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.

[v-pnaroju-msft]

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

[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]

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?... "Hide Blanks Values in Matrix - Hierarchy"

[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 😶

[Shahid12523]

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.

[katina]

Unfortunately @shasha , 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]

This sounds clever, I'll try it right now and comment then. Thanks!

[govind_021]

Hey , @katina 
Try using SELECTEDVALUE(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 🙂