Forum Discussion
Remove blank row level in Matrix visual and keep the data
I want the matrix to show only the levels that actually contain data.
For example, if the data only goes up to Level 7, I don’t want Level 8 and Level 9 to appear as blank rows.
This is example data, I try to do as per this solutin https://community.fabric.microsoft.com/t5/Desktop/Remove-blank-rows-from-matrix-but-still-contain-data/m-p/4914338 still work but not effective,
Thank you
Hi Jidapa18 ,
Thanks for sharing the comparison and screenshots.What you’re seeing in the “Remove Blank Row Level” visual is expected. When a level is removed because it has no value, it is no longer part of the matrix. When you expand or drill, only the remaining visible levels are used, so some parent levels can be skipped even though the lowest level still shows a value.
In the “Not Remove Blank Row Level” case, all levels are kept, so the full expand path is shown. This isn’t caused by the measure, since the value is correct in both visuals. It’s a limitation of how the matrix works when parent levels are hidden.
If you need the expand or collapse behavior to stay consistent, the parent levels must remain visible. Once they are removed, they can’t be brought back during drill.
As an alternative, you can switch the matrix layout to Tabular. This shows each level in its own column and avoids expanding into empty leaves. It changes the layout and doesn’t keep the stepped drill behavior, but it can work depending on the requirement.
Hope this helps clarify the behavior and available options.
8 Replies
Hii Jidapa18
In a Matrix visual, Power BI always renders all hierarchy levels added to the Rows well, even when lower levels contain no data. This behavior is by design and cannot be controlled using “Show items with no data” or simple blank filters. The recommended approach is to control row visibility using a measure and apply it as a visual-level filter.
Has Data = IF ( NOT ISBLANK ( [Your Main Measure] ), 1, BLANK() )Add this measure to Filters on this visual and set it to is not blank. This ensures that only hierarchy levels that actually contain data are shown, and empty lower levels are hidden automatically.
I created that solution. It works but not effective? You need to tell us why it isn't.
Hi danextian Your solution can solve my problem before but for now I found one issue that need to be improve, I compare for both side are "Remove Blank Rows" and "Not Remove Blank Rows" as picture
In this picture if you see in the row Subway both side will show value OR-5, but when I click drill up or drill down for expand or collaspe level in the "Remove blank row side" not show the level before the value, I compare as the picture.
So If you can help me for solve this issue, It would be appreciate.
Thanks a lot.Hi Jidapa18 ,
Thanks for sharing the comparison and screenshots.What you’re seeing in the “Remove Blank Row Level” visual is expected. When a level is removed because it has no value, it is no longer part of the matrix. When you expand or drill, only the remaining visible levels are used, so some parent levels can be skipped even though the lowest level still shows a value.
In the “Not Remove Blank Row Level” case, all levels are kept, so the full expand path is shown. This isn’t caused by the measure, since the value is correct in both visuals. It’s a limitation of how the matrix works when parent levels are hidden.
If you need the expand or collapse behavior to stay consistent, the parent levels must remain visible. Once they are removed, they can’t be brought back during drill.
As an alternative, you can switch the matrix layout to Tabular. This shows each level in its own column and avoids expanding into empty leaves. It changes the layout and doesn’t keep the stepped drill behavior, but it can work depending on the requirement.
Hope this helps clarify the behavior and available options.
