Want to filter sub-ordinates under each manager when both are in different tables

[AshDil]

Hi,

I have two tables, table one is having managers and other people. Table-2 is having subordinates.

Table-1:

ID	Name	Is_Manager	Group
1011	Arsha	Yes	A
1012	Jack	no	A
1013	John	Yes	B
1014	Madrid	Yes	B
1015	Sam	Yes	C
1016	Anudeep	No	C
1017	Gaurav	No	C

Table-2:

Subordinate_ID	Name	Group
10101	Ashish	A
10102	Siddarth	A
10103	Naveen	A
10104	Paul	A
10105	James	B
10106	Samson	B
10107	Nisha	B
10108	Veena	B
10109	Rahul	C
10110	Pooja	C
10111	Ravan	C
10112	Eric	C

I want to show manager and sub-ordinates in each group in a table 

Group	Manager	Sub-Ordinate
A	Arsha	Ashish
A	Arsha	Siddarth
A	Arsha	Naveen
A	Arsha	Paul
B	John	James
B	John	Samson
B	Madrid	Nisha
B	Madrid	Veena
C	Sam	Rahul
C	Sam	Pooja
C	Sam	Ravan
C	Sam	Eric

and also in slicers in another tab

Please help me to do it.

[visheshjain]

Hi @AshDil ,

My suggestion would be to go for many-to-many single direction relationship, if there absolutely no possibility of you having a one to many single direction relationship.

The only problem I see in achieving 1-* single direction relationship is that in case of Group B managers, you have both John and Madrid as managers and in the same group, so PBI will not be able to determine, which manger to choose for which employee.

I am assuming there is no mistake in your data and if this can be corrected then go for 1-* single direction, else many-to-many (*-*) single direction relationship.

Hope this helps

Thank you,

Vishesh Jain

[AshDil · Accepted solution]

Hi @visheshjain & @amitchandak ,

I have resolved this problem myself.

There is no mistake in data, John and Madrid were present in same group. I created a calculated column manually to differentiate subordinates under each manger and then created hierarchy for Group, Manager and Sub-ordinate. 

Thanks for your timely response.

Thanks,

AshDil.

[v-chenwuz-msft]

Hi @AshDil ,

Glad your problem is solved, can you share your solution and mark it as a solution? So that more people with the same problem as you can find the answer quickly.

[visheshjain]

Hi @AshDil ,

Just wanted to know how you created the calculated column or did you add the column to your source data itself?

Thank you,

Vishesh Jain

[AshDil]

Hi @visheshjain ,

I have added a column to source data itself based on which HR they are reporting. John and his subordinates report to one HR likewise Madrid and his subordinates report to another HR. 

Thanks,

AshDil.

[amitchandak]

@AshDil , Join both of them on Group . Many to many bi-directional join will be there.

Where you put those into visual you can filter for Is_Manager = Yes and put required columns in visual

[AshDil]

Hi @amitchandak ,

Power Bi is throwing error when I tried to join these both, since they have relationship with other tables in common on other fields.

Please let me know any other way to do it.

Thanks,

AshDil