Multilevel Hierarchy(Managerial Hierarchy) - Manager and Employee

[SivaMani]

Hi,

Sample Data:

DirectManager	Employee	Expense	MgrID	PersonID
Mgr1	Mgr2	100	1001	1002
Mgr2	Mgr3	50	1002	1003
Mgr2	Emp1	25	1002	1004
Mgr3	Emp2	30	1003	1005

I need to create a table containing mutilevel hierarchy .I used PATH function to achieve that , but it throws up an error stating Mgr 1 should also exist in Employee column. Need some assistance in this.

Thanks in advance.

[Zubair_Muhammad]

@SivaMani 

You will need to add Mgr1 both as an Employee and DirectManager for PATH function to work

If you cannot directly input this row in your database,

You can add a row with Power Query by adding this step from the Query Editor (File attached)

 AddRow = Table.Combine({#"Changed Type",#table(type table[ #"DirectManager"=text, #"Employee"=text], {{"Mgr1","Mgr1"}})})
in
  AddRow

[SivaMani]

Thanks @Zubair_Muhammad .

I have resolved this issue with a similar approach. 

I have another challenge to visualize this data. I need to represent this in a Matrix visual. Here is the example,

[-] Mgr1

   [-] Mgr2

       Emp1

      [+] Mgr 3

I created 3 new columns and attained this requirement using PATHITEM function. Is there any alternative approach to minimize this effort?

[Zubair_Muhammad]

Hi @SivaMani 

Alternate is to use Power Query to replicate the PATH function.

Then we can split the PATHs in one step into multiple levels

This avoids the problem of having to manually create PATH levels when we use PATH (DAX) function

I wrote a blog post about it

http://www.excelnaccess.com/replicating-path-function-of-dax-in-power-query-m/

This approach could be slow for large datasets since it uses a recursive function.
But (I believe) since PATH function is applied to DIMENSION tables mostly...this approach can be very handy

[SivaMani]

Great!

Thanks @Zubair_Muhammad .

[SivaMani]

Hi @Zubair_Muhammad ,

I have tested M query option. It is too slow. My dimention data file size is 715 KB with 8 columns and 8410 records.

After I applied the function, it is processing more than 11 GB and it seems to never end.

[Zubair_Muhammad]

@SivaMani 

Could you share your file with me?

[SivaMani]

Sorry @Zubair_Muhammad .

I can't share my file - it contains senstive data. I'm facing this problem as a common issue.