Hello everyone,
I've been browsing past threads about hiding blank values in matrix with hierarchical data but i think i have a bug in doing so.
After i flag all blank lines, when i try to filter them out, all the values dissapear.
I have no idea why.
Here's an image showing the issue.
Hi , @iLoveTea
According to your image, we can not make this measure return 1 and 0 and then filter it.
I test this method before,it dose not work the same as yours.
The method should return the value you need, you need to update this measure return teh value which this column need.And you need to replace all the column by the measures.
That means you need to create two measures like that to replace this two yellow columns.You need to replace the 1 to your need value in the measure .
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I picked some screenshots and try to make it clearer what happens here.
Here in the example, I took numerical IDs instead of names, though:
I took the number of children as the hierarchy instead of the things in the accepted answer's example.
The measure in the right column is:
```
replace id (measure) = IF( ISINSCOPE(myTbl[number_of_children])&& SELECTEDVALUE(myTbl[number_of_children])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_1])&& SELECTEDVALUE(myTbl[number_of_children_1])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_2])&&SELECTEDVALUE(myTbl[number_of_children_2])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_3])&& SELECTEDVALUE(myTbl[number_of_children_3])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_4])&& SELECTEDVALUE(myTbl[number_of_children_4])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_5])&& SELECTEDVALUE(myTbl[number_of_children_5])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_6])&& SELECTEDVALUE(myTbl[number_of_children_6])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_7])&& SELECTEDVALUE(myTbl[number_of_children_7])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_8])&& SELECTEDVALUE(myTbl[number_of_children_8])=BLANK(),BLANK(),IF(ISINSCOPE(myTbl[number_of_children_9])&& SELECTEDVALUE(myTbl[number_of_children_9])=BLANK(),BLANK(),MAX(myTbl[id])))))))))))
```
I need *one* name in *each* row where the level is filled since each node has a name that I want to show, and on the left, I will take the level numbers or number of children as the hierarchy (or I will change to something else like the names in the hierarchy later; but I want names in other languages listed in the columns). One way is to put an id as another item in the hierarchy on the left so that MAX() does not aggregate anything, every row is spread by the identifier, and this will show the ids in the leaves:
The formula in the accepted answer might work well. I did not check it and trust its screenshots that show clearly that it works. But in my setting, if I check each level for `= BLANK(), BLANK(), else MAX()`, as in the accepted answer, every blank level that is found in that formula drops its whole branch:
Thus, if I write the formula shorter, just for level 0 to 4, nodes that have *only* such filled levels will show up:
And if I now check for level 5 to be BLANK(), all of the branches that do not have level 5 are dropped, which means in my data that just four branches are left. This does not show any empty nodes, but it also just filters for all that have *only* five levels. The rest is dropped. I see that in the accepted answer, it works better, there you have a depth of two and four, and the one of depth two does not have a [+] sign so that the last level is really shown without any empty levels coming after it. I can tweak a nice graph here if I just choose 5 out of 9 levels. You would think at first sight, that it works:
But that is only a bad trick here. I want to show all branches of any length, not just those of length 5.
If a level is not filled (if the level is not in the data at all), it should not be shown. Yet, the empty nodes *are* shown. The measure works "a bit" like in the answer, but not in full: if I put the new measure with just four levels in the matrix values, the tree gets reduced automatically to the filled levels for some, but for the most, some empty levels are still shown. And as soon as I ask to drill down all nodes to their leaves, all of the empty levels are shown again, and I cannot get rid of them anymore without starting from new by taking out the measure and putting it in again. In the accepted answer's image, I can see that the tree really stops at the leaf level without showing a [+] box that you still could click to see the empty levels below it. But when I do this, the tree gets reduced to the filled levels, and the [+] box is shown:
And I can drill down to:
The empty boxes are shown again, and you cannot get rid of them easily unless you return to PBI and put the measure out and in again.
I found it. You need to switch on the row totals of the matrix. I only understood that this would fix it when I rebuilt the example of the accepted answer. Without the row totals, you get less nodes, the master1 node is missing. This is since the formula drops any path that has a blank in the first four levels:
And with row totals, you get the needed rows for the master1 branch as well:
This fixes it also in the hierarchy model of my own data.
And then it also worked with my data. I flatten the data already in SSIS, if you do not want that, you can flatten it in Power BI and need one "step 0" more, see the first link.
Links:
- Parent-child hierarchies – DAX Patterns
- Solved: Hide blank values in hierarchy matrix - Microsoft Fabric Community
1.
```
4. (Example)
This shows the number of Children inside a root tree for each node if in the hierarchy, there are always two nodes for each level, first the name and then the number of children, nested right after each other, so that there are 9x2 nodes in the hierarchy:
```
Hi @amitchandak @v-yueyunzh-msft
I tried amitchandak but it didnt work
I tried yueyunzh but i dont have a value column to leverage.
here's what i have :
In field i have 9 categories.
The removeblank measure is :
_Remove Blanks =
SWITCH(TRUE(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat2]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat2]))),BLANK(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat3]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat3]))),BLANK(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat4]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat4]))),BLANK(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat5]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat5]))),BLANK(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat6]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat6]))),BLANK(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat7]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat7]))),BLANK(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat8]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat8]))),BLANK(),
AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat9]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat9]))),BLANK(),
1)
When i filter the blanks in the filter panel, that happens:
What i would like to achieve is that
Thank you for your help
Hi , @iLoveTea
According to your image, we can not make this measure return 1 and 0 and then filter it.
I test this method before,it dose not work the same as yours.
The method should return the value you need, you need to update this measure return teh value which this column need.And you need to replace all the column by the measures.
That means you need to create two measures like that to replace this two yellow columns.You need to replace the 1 to your need value in the measure .
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @iLoveTea
According to your description, you want to "Hide blank values in hierarchy matrix". Right?
Here is a method you can refer to :
(1)This is my test data:
(2)We can create a measure to replace the value in your visual:
Replace sum of value = IF( ISINSCOPE('Table'[Type1])&& SELECTEDVALUE('Table'[Type1])=BLANK(),BLANK(),IF(ISINSCOPE('Table'[Type2])&& SELECTEDVALUE('Table'[Type2])=BLANK(),BLANK(),IF(ISINSCOPE('Table'[Type3])&&SELECTEDVALUE('Table'[Type3])=BLANK(),BLANK(),IF(ISINSCOPE('Table'[Type4])&& SELECTEDVALUE('Table'[Type4])=BLANK(),BLANK(),SUM('Table'[Value])))))(3)Then we can put the field on the visual :
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
