Ragged Hierarchy - When Remove Blank Hierarchy, the child's are removed too

[Anonymous]

in the following matrix, I need to remove blank levels without removing its details/ children

what I did is use ISINSCOPE as follows to calculate the values

as a result, blank levels are removed but childs are removed too

any idea what could I do ?

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

Based on your description, you want not to display parent Row headers. I tested locally with me. There is probably no way to implement this requirement at the moment, unless you refactor your hierarchy so that the middle layer has no null values.

This is my test data:

I use this dax code:

Measure = 
var _c4 =MAX('Table'[Column4])
var _c5= MAX('Table'[Column5])
var  _c3 =MAX('Table'[Column3])
var _c2 =MAX('Table'[Column2])
var _c1 =MAX('Table'[Column1])
return
SWITCH(TRUE(),
ISINSCOPE('Table'[Column5]) && _c5 <> BLANK(), SUM('Table'[Value]),
ISINSCOPE('Table'[Column4]) && _c4 <> BLANK(),SUM('Table'[Value]),
IF( ISINSCOPE('Table'[Column4]) ,FALSE(), IF( ISINSCOPE('Table'[Column3]) && _c3 <> BLANK() ,TRUE())) ,SUM('Table'[Value]),
IF( ISINSCOPE('Table'[Column3]) ,FALSE(), IF( ISINSCOPE('Table'[Column2]) && _c2 <> BLANK() ,TRUE())) ,SUM('Table'[Value]),
IF( ISINSCOPE('Table'[Column2]) ,FALSE(), IF( ISINSCOPE('Table'[Column1]) && _c1 <> BLANK() ,TRUE())) ,SUM('Table'[Value]),
BLANK() )

Even if null can be returned at the corresponding parent level, for Matrix, as long as there are values in the child level, the parent level seems to be displayed.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

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

[Anonymous]

Thanks for your time 🙏