Flattened Multi Level Hierarchy to only show the last item in the path

[ogend]

Hi Power BI Gurus, 

I have a multi level hierarchy table:

ChildID	ChildName	ParentID	ParentName
a	AAA
b	BBB	a	AAA
c	CCC	a	AAA
d	DDD	c	CCC
e	EEE	d	DDD
f	FFF	d	DDD

I can create a flat view  with  calculated columns:

path = path('Table'[ChildID ],'Table'[Parent ID ] )

Level1 = LOOKUPVALUE('Table'[ChildName ],'Table'[ChildID ],PATHITEM('Path'[path],1,INTEGER))

Level2 = LOOKUPVALUE('Table'[ChildName ],'Table'[ChildID ],PATHITEM('Path'[path],2,INTEGER))
Level3...

ChildID	Level1	Level2	Level3	Level4
a	AAA
b	AAA	BBB
c	AAA	CCC
d	AAA	CCC	DDD
e	AAA	EEE	DDD	EEE
f	AAA	FFF	DDD	FFF

I would like to get to this view where the Level value only shows if it corresponds to the last ( or only) item in the path. 

ChildID	Level1	Level2	Level3	Level4
a	AAA
b		BBB
c		CCC
d			DDD
e				EEE
f				FFF

Is this possilbe? 

Thank you

[Anonymous]

Hi @ogend ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[ALLUREAN · Accepted solution]

Hi, @ogend 

You can try something like:

https://www.dropbox.com/s/tdxmio6vj9fg7m8/Flattened%20Multi%20Level%20Hierarchy%20to%20only%20show%2...

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[lbendlin]

Query PATHLENGTH() and then fetch the last item.