Forum Discussion
Resident RockstarMultilevel Hierarchy(Managerial Hierarchy) - Manager and Employee
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.
7 Replies
- Zubair_Muhammad
Community Champion
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
SivaManiThanks 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
