Forum Discussion
Remove empty hirearchy levels
HI! Im trying to build a custom Matrix from a table, so that the empty hierarchy levels do not display.
| Category 1 | Category 2 | Category 3 | Value |
| BMW | Black | New | 1 |
| BMW | White | 2 | |
| Audi | Red | Used | 2 |
What Im betting now for the second row is
- BMW-White-BLANK-2
How could I remove the blanks in between from the hierarchies?
Thanks for the help!
Hi JP_Helsinki,
The design pattern you're looking for is a ragged hierarchy, and Power BI doesn't support it natively. It can be managed with a number of additional modelaling changes and some measures to check which level of the hierarchy a value is on.
The following articles are my go-tos whenever I need to implement such a pattern, and should help you out:
- SQLBI: Parent-Child Hierarchies
- Kasper on BI: Use IsInScope to get the right hierarchy level in DAX
- RADACAD: Removing Blanks from Organizational Ragged Hierarchy in Power BI Matrix Visual
Good luck 🙂
Daniel
1 Reply
Hi JP_Helsinki,
The design pattern you're looking for is a ragged hierarchy, and Power BI doesn't support it natively. It can be managed with a number of additional modelaling changes and some measures to check which level of the hierarchy a value is on.
The following articles are my go-tos whenever I need to implement such a pattern, and should help you out:
- SQLBI: Parent-Child Hierarchies
- Kasper on BI: Use IsInScope to get the right hierarchy level in DAX
- RADACAD: Removing Blanks from Organizational Ragged Hierarchy in Power BI Matrix Visual
Good luck 🙂
Daniel