Forum Discussion
Having issue with Ragged hierarchy in matrix visualization
In Power BI, we are trying to create a hierarchy for organizational roles (e.g., Level 1 → Level 2 → Level 3 → Level 4 → Claim Team → Handler). However, the data is not uniform:
- Some records only have up to Level 2
- Others stop at Level 3
- A few go as deep as Level 6
The problem is that visuals like Matrix tables or Tree Maps still reserve space for all levels, resulting in blank rows or columns for missing levels. This seems to be related to how Power BI handles ragged hierarchies.
Can any one help me here
Hi Vasudeva-cv19,
Thank you for the response. This behaviour is expected because the Matrix visual does not support true ragged hierarchies. Unfortunately it cannot hide empty hierarchy levels automatically and no DAX filter can override that behaviour.
To display the hierarchy without blanks, the data needs to be reshaped so that only the levels that actually exist are used in the visual. Please follow below steps:
- Use Power Query and unpivot Level_1 to Level_6 and remove the blank values. This shows a clean parent-->child structure and the Matrix will show the hierarchy correctly without empty spaces.
- If you cannot change the shape, please create a single Path column like combine only the non-blank levels into a single text for example like Vasudeva > Mayur > Caarly > ADAMAS. Then use this Path column in the matrix or table. It avoids all blank levels because the path is built only from existing values.
Thanks and regards,
Anjan Kumar Chippa
11 Replies
Vasudeva-cv19 Hard to be certain without sample data and expected output but you should be able to eliminate unwanted rows using ISINSCOPE within a Complex Selector: The Complex Selector - Microsoft Fabric Community
this is my sample data where some of them have level 6 and some of the stopped at level 1 or level 2
Level_1_Team Level_2_Team Level_3_Team Level_4_Team Level_5_Team claim_team_name Handler_name Active Claims Dates ANDREW-DANE FAIRCLOUGH 2 CLIVE BUTCHER 1 EMMA SARDESON 1 KEITH BATTSON 1 TING LOW 1 WILL EVANS 1 Historic ALLEGRA SANTOMAURO 1 Historic LYNETTE JEN 1 Historic PATRICK MURPHY 1 Historic SHAMICA ALEONG 1 UNCODED/OTHER CARLY ROBINSON 1 Vasudeva Colm PAUL Avia JONNA HOLM 206 Vasudeva Colm PAUL Avia JOSEPH MCCULLOUGH 53 Vasudeva Colm PAUL Avia KILEY HACKER 1 Vasudeva Colm PAUL Avia MARTIN REED 123 Vasudeva Colm PAUL Avia MASON ALBA 60 Vasudeva Colm PAUL Avia OLIVIA VISEE 32 Vasudeva Colm PAUL Avia PATTY PARTHEYMULLER 73 Vasudeva Colm PAUL Avia QUENTIN KAREIVA 1 Vasudeva Colm PAUL Avia RONALD KELEMAN 179 Vasudeva Colm PAUL Avia SUBROGATION EXAMINER 99 Vasudeva Mayur PAUL Avia ZOE GARRISON 1 Vasudeva Mayur DS ERIC LEVINE 3 Vasudeva Mayur DS MARJORIE THOMPSON 35 Vasudeva Mayur DS RICHARD TOWLE 12 Vasudeva Mayur DS ROBERT RICCOBONO 22 Vasudeva Mayur Caarly Caar DAVID COLWICK 5 Vasudeva Mayur Caarly Caar ERIKA MASCARO 5 Vasudeva Mayur Caarly Caar JOANNE WAYMAN 138 Vasudeva Mayur Caarly Caar KEVIN FISHER 27 Vasudeva Mayur Caarly ADAMAS Dohe DAVID LEVESQUE 168 Vasudeva Mayur Caarly ADAMAS Dohe DAVID RETTIG 1 Vasudeva Mayur Caarly ADAMAS Dohe DOEXCESS SEGMENTATION 10 Vasudeva Mayur Caarly ADAMAS Dohe FFIONA MCDONOUGH 202 Vasudeva Mayur Caarly ADAMAS Dohe JAMES SIEWERT 225 Vasudeva Mayur Caarly ADAMAS Dohe JOSEPH GIRDUSKY 113 Vasudeva Mayur Caarly ADAMAS Dohe TRACY MONTALBANO 234 Vasudeva Mayur Caarly ADAMAS JOSEPH Girdu ANTHONY ZALESKI 207 Vasudeva Mayur Caarly ADAMAS JOSEPH Girdu ROBERT ISABELLA 118 This is my sample data -- I need Vasudeva-->Mayur ..> + Carly +
+ Paul +
Like this hierachy without blanks are needed Greg_Deckler
Vasudeva-cv19 So why not a selector measure like the following?
Selector Measure = VAR __Level_3_Team = SELECTEDVALUE( 'Table'[Level_3_Team] ) VAR __Level_4_Team = SELECTEDVALUE( 'Table'[Level_4_Team] ) VAR __Level_5_Team = SELECTEDVALUE( 'Table'[Level_5_Team] ) VAR __Result = IF( __Level_3_Team = BLANK() || __Level_4_Team = BLANK() || __Level_5_Team = BLANK(), 0, 1 ) RETURN __ResultPut that measure in your Visual level filter and set the filter to equal 1. Then the unwanted rows should disappear from the matrix.
Hi Vasudeva-cv19,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Greg_Deckler for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
This is not helpfull for as i am looking for hierarchy
its classic ragged hierarchy problem ..Some out of box resoultion required as we have balnks at multilevels
