How do you show an organization hierarchy?

[Anonymous]

Hi,

I've a below example dataset.

Employee_ID	Employee_Name	Manager's Employee_ID
A001	Stuart Paul	B001
A003	Heather T	C001
B001	Robert G	C001
Z001	Doug F	Z001
D001	Tom S	B001
C001	Danny Duke	Z001

I'm trying to show the hirerarchy for example

1. Lets say I pick an employee with their ID I would need to know their hirerarchy until it ends at CEO. Based on the above example if I pick Stuart I should get

Sturart reports to > Robert > Danny > Doug (CEO)

2. Also if I pick a manager I would want to know the hierarchy as above and I would also want to know the subbordinates of that manager for example

a. Let's say I pick "Robert"

      I want something like below

•  Stuart and Tom Report to Robert > Danny > Doug

b. Let's say I pick "Danny" I want the hirerarchy to show something like below.

Employee has a Manager. Manager is an Employee and in turn reports to an Executive who is also an Employee.

There might be n level of hirararchy.

I am having troble to model the data in such a way which displays the required hirerarchy.

Please advise.

Thanks

[amitchandak]

@Anonymous , Refer if this can help

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

[Anonymous]

@amitchandak 

The soluton in that link gives me just the top hirerarchy but I'm mainly looking for reportees (subordinates) as well.

Lets say if I pick a manager I want to know whom does he report to and all the subbordinates of him as well.

[V-lianl-msft]

Hi @Anonymous ,

Create an unrelated Employee_ID table as slicer.

Table 2 = DISTINCT('Table'[Employee_ID])

Use the path function to find the entire path from one member in original table and create Path length column.

Path = PATH('Table'[Employee_ID],'Table'[Manager's Employee_ID])
Path Length = PATHLENGTH('Table'[Path])

Create a measure to determine whether it is the longest path and apply it in visual level filter.

Measure = IF(SEARCH(SELECTEDVALUE('Table 2'[Employee_ID]),MAX('Table'[Path]),1,0)>0&&MAX('Table'[Path Length])=CALCULATE(MAX('Table'[Path Length]),ALL('Table')),1,0)

Sample .pbix

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

[Anonymous]

I'm showing the details in a matrix and the measure would not work. For example if I pick A003 it wouldnt work for that Employee

[V-lianl-msft]

Hi @Anonymous ,

Sorry, according to the example you provided and the result you expect, I can test it out.

Please refer to the sample pbix I shared to check if any steps have been omitted.

You can also calculate the real data with the method like this.

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

[V-lianl-msft]

Hi @Anonymous ,

Is this problem sloved?
If not, please let me know.

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

[Anonymous]

@V-lianl-msft I did check the Sample.pbix and when I select A003 the path is empty which is not correct right?

So the idea which I came up with is create a path and put a text search on that so now I can search for an ID and it shows all the values associated to that ID including A003.