Forum Discussion
Avoid blanks on a hierarchy without modifying context
I’m working with a network hierarchy in Power BI and I’m struggling to find a clean implementation using the Matrix visual without breaking filter context.
I have a table (`network_hierarchy_current`) with a flattened structure like:
When I try to remove NULL elements in the matrix (for example, filtering out blank feeders):
I need a solution that satisfies ALL of the following:
- Preserve context: The visibility logic must NOT modify the filter context
Think of the hierarchy as a single row made up of multiple columns, just like how it appears in a table, not as separate rows the way a matrix visual displays it. Because of that, if you filter out a blank in one column, you’re not just hiding that one value. You’re effectively removing the entire row. So any other columns that also have blanks on those same rows will disappear too, since those rows no longer exist in the data after filtering.
If my understanding is correct, I don't think what you want is possible with measures alone. You'll need to create a new table that skips the blank hierarchy and move the next level to where the blank is. Attached is a sample solution which is of course based on a very simple data - it can get complex with a hierarchy that involves multiple tables.
7 Replies
Think of the hierarchy as a single row made up of multiple columns, just like how it appears in a table, not as separate rows the way a matrix visual displays it. Because of that, if you filter out a blank in one column, you’re not just hiding that one value. You’re effectively removing the entire row. So any other columns that also have blanks on those same rows will disappear too, since those rows no longer exist in the data after filtering.
If my understanding is correct, I don't think what you want is possible with measures alone. You'll need to create a new table that skips the blank hierarchy and move the next level to where the blank is. Attached is a sample solution which is of course based on a very simple data - it can get complex with a hierarchy that involves multiple tables.
Hi InmaVM,
Thank you danextian mussaenda Rupa01 and parry2k for your prompt responses to the querey
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If you need any further assistance, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
InmaVM not sure what you mean by the following highlighed text:
## Problem
When I try to remove NULL elements in the matrix (for example, filtering out blank feeders):
- The hierarchy looks cleaner- BUT it **modifies the filter context**- This breaks:- Other visuals (tables, KPIs)- Measures based on fact tablesHi InmaVM,
When working with multi-level hierarchies in a Power BI matrix, blank or null values often appear as empty nodes.
A reliable way to handle this is by using ISINSCOPE to detect the current hierarchy level and return a value only when that level is populated.🔷 Sample Data
Below is an example table with 5 hierarchy levels (A → E) containing blank or null values.
A B C D E A101 B101 C101 null null A201 B201 null null null A301 B301 C301 null null 🔷 DAX Measure
The following measure (HideBlankHierarchyRows) uses ISINSCOPE to identify the current level and removes blank/null nodes by returning the hierarchy depth:
HideBlankHierarchyRows = VAR Depth = SWITCH( TRUE(), ISINSCOPE('Table'[E]), 5, ISINSCOPE('Table'[D]), 4, ISINSCOPE('Table'[C]), 3, ISINSCOPE('Table'[B]), 2, ISINSCOPE('Table'[A]), 1 ) VAR CurrentValue = SWITCH( Depth, 5, SELECTEDVALUE('Table'[E]), 4, SELECTEDVALUE('Table'[D]), 3, SELECTEDVALUE('Table'[C]), 2, SELECTEDVALUE('Table'[B]), 1, SELECTEDVALUE('Table'[A]) ) RETURN IF(NOT ISBLANK(CurrentValue) && CurrentValue <> "", Depth)💡 Note: If you need to display a business metric instead of the hierarchy depth, you can replace the values returned for each level (Depth Variable) with your required calculation based on the level.
🔷 Result
Below screenshot shows how the blank or null values are removed using the HideBlankHierarchyRows measure -
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics EngineerHi InmaVM ,
Do null elements have their corresponding values in measures (assuming that null elements are from a column)? Because if they have, it is either you need to assign a value to it or if they are unnecessary, then you can remove them. It really depends on your requirements.
Hope this helps.
Hi , thnks for your answers.
Let me share my current semantic model and the report to better understand the topic:
Network tree matrix component is based on 'network_hierarchy_current' table.
'network_hierarchy_current' table is in the center of my schema and 'catch' filter from the dimensions (SS, TX,..) and propagates to fact tables 'Network Issues'.
Below I will describe two scenarios sharing a screenshot where we can see 3 main components: the network tree, a matrix with network elements with issues and a table just showing 'network_hierarchy_current' content table to better ilustrate the topic.
- Scenario A: showing nulls on the tree.
In this case, nulls are shown on the tree so I can see issues for Substation and Transformer on the right matrix as well as rows for those elements on network_hierarchy_current the table:
-Scenario B: Nulls removed from the matrix by applying filter 'Feeders name' is not blank
In this case, SS and TX rown was removed from 'network_hierarchy_current' because SS and TX elements have 'lv_feeder_sk' null on data base. This removal of rows is propagated to my fact table breaking issues calculations (due to my semantic model design).
Is there any way to:
- not display null elements on the tree
- keep the semantic model in that way
- not modify the context. I mean, don´t remove rows with null column from network_hierarchy_current.
I hope is clearer now. Thanks
Hi InmaVM,
Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help.
Thank you and Continue using Microsoft Fabric Community Forum.
