Modelling with a complex deep organisational hierarchy

[Anonymous]

Hey all,

PBI noob here.

I am tripping over myself on how to approach the modelling for my organisation lookup table. The org structure is 4 layers deep. The data is manually created in excel, I am wondering how to best approach this to allow the drill down ability. Should I structure the data in excel a certain way or is there a better way to handle it in PBI?

This is an example of how the excel file is structured excluding top level... I have 12 Level 2 Groups and a myriad of different combinations of Level3/4.

Level 2 group A	Level 3 Group AA	Level 4 Group AAA
		Level 4 Group AAB
	Level 3 Group AB	None
	Level 3 Group AC	Level 4 Group ACA
		Level 4 Group ACB
		Level 4 Group ACD
Level 2 Group B	Level 3 Group BA	None
	Level 3 Group BB	Level 4 Group BBA
	Level 3 Group BC	None

[amitchandak · Accepted solution]

@Anonymous 

Refer: https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-child-functions-in-dax

https://radacad.com/removing-blanks-from-organizational-ragged-hierarchy-in-power-bi-matrix-visual

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi