Remove empty hirearchy levels

[JP_Helsinki]

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!

[dm-p · Accepted solution]

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