How to flatten a hierarchy in PowerBI?

Question

Imagine this table:

Level	Name	Parent
LevelA	Fruit
LevelB	Apple	Fruit
LevelA	Moving
LevelB	FourWheelers	Moving
LevelC	Car	FourWheelers
LevelC	Truck	FourWheelers

what I try to achieve is a hierarchy which looks like that:

Fruit

Apple

Moving

FourWheelers

Car

Truck

Whatever I tried so far, it didn't work out. Especially I need the level as independed attribute so that I can e.g. create a widget for level B only.

Answer 1

You can follow the pattern here to flatten a hierarchy.

https://www.daxpatterns.com/parent-child-hierarchies/

Create 4 calculated columns as follows:

EntityPath = 
PATH ('Table'[Name], 'Table'[Parent] )


Level1 = 
VAR LevelNumber = 1
VAR LevelKey = PATHITEM ( 'Table'[EntityPath], LevelNumber )
RETURN
    LevelKey


Level2 = 
VAR LevelNumber = 2
VAR LevelKey = PATHITEM ( 'Table'[EntityPath], LevelNumber )
RETURN
    LevelKey


Level3 = 
VAR LevelNumber = 3
VAR LevelKey = PATHITEM ( 'Table'[EntityPath], LevelNumber )
RETURN
    LevelKey

Use the new columns in your hierarchy.