Look for selected person in the Hierarchy Path and show direct/indirect employees under him

[LP280388]

Hi Team,

We have a need for creating a Slicer where the user selects a particular employee and the table visual should show all the employees reporting directly or indirectly to the employee.

Currently, I have 2 tables

1.  with Person ID, Employee ID, Manager ID along with Path column

2.  Employee ID, Sales, Sale Date

Person ID is the Unique ID used to derive the path.  Employee ID is the one used in the slicer.  

In a nutshell,  the user selects an Employee ID in the Slicer, whose Person ID has to be captured and find in the Hierarchy path and then show the employees who are directly/indirectly reporting to the employee id selected.

Kindly help how this can be achieved.

[EmiljoJ]

Hi @LP280388, 

did you already find a solution to this topic? 

[LP280388]

Hi @EmiljoJ , we ended up creating a hierarchy table in DB itself. 

like if A has 3 direct reportees and 2 indirect reportees, then we will have 5 records under his name.

[v-jingzhang]

Hi @LP280388 

Can you mock up some dummy data and paste them in the post to show the data you have? And how did you create the Path column? With DAX or in the data source? If the Path column is created in the data source, I'd like to transform the table with Power Query to deal with it. However if it's created with DAX, we can only try to find DAX solutions with it. 

Best Regards,
Community Support Team _ Jing

[LP280388]

Hi @v-jingzhang 

Thanks for the response. 

Below is the sample data. 

Employee Table : 

Path = PATH(EmployeeData[Person ID],EmployeeData[Manager ID])

EmployeeData
Employee ID	Person ID	Manager ID	Path
10001	1
10002	2	1	1
10003	3	2	1|2
10004	4	2	1|2
10005	5	2	1|2
10006	6	2	1|2
10007	7	3	1|2|3
10008	8	3	1|2|3
10009	9	3	1|2|3
10010	10	6	1|2|6
10011	11	6	1|2|6
10012	12	6	1|2|6

TrainingsData :

Training ID	Employee ID
Training1	10001
Training2	10002
Training3	10005
Training4	10003
Training5	10004
Training6	10005
Training7	10003
Training8	10004
Training9	10005
Training10	10006
Training11	10007
Training12	10008
Training13	10003
Training14	10004
Training15	10005
Training16	10006
Training17	10007
Training18	10008
Training19	10009
Training20	10010
Training21	10011
Training22	10012

Output expected is : 

A slicer with EmployeeID  and a table visual. 

when filter for employee ID "10002", the table visual should show below trainings which are taken by his direct and indirect reportees.

Employee ID	Trainings ID
Training3	10005
Training4	10003
Training5	10004
Training6	10005
Training7	10003
Training8	10004
Training9	10005
Training10	10006
Training11	10007
Training12	10008
Training13	10003
Training14	10004
Training15	10005
Training16	10006
Training17	10007
Training18	10008
Training19	10009
Training20	10010
Training21	10011
Training22	10012