Duplicate Manager Names at Different Levels

[Anonymous]

Hello PowerBI Community,

I’m currently working on a report where I have a table of users and their respective managers at different levels. Each row represents a user and their managers, where “Manager - Level 1” is the direct manager they report to, “Manager - Level 2” is the manager that “Manager - Level 1” reports to, and so on.

My goal is to create a slicer in my report that allows filtering by manager names. Ideally, selecting a manager’s name in the slicer would filter the report to show only the users that report to that manager.

However, I’m encountering an issue where the same manager appears at different levels for different users. This results in the slicer displaying duplicate entries for the same manager. For instance, if a manager is at “Level 1” for one user and at “Level 4” for another, that manager’s name appears twice in the slicer at different levels and in turn shows different users.

To illustrate this further, let’s say I have four names in my slicer. When I click on one of these names, I can see more names (i.e., the managers under the manager I just clicked). But because some users have a manager at “Level 1” and others have the same manager at “Level 4”, I end up with multiple entries of the same manager in the slicer.

I’m looking for a solution that would allow me to filter by manager names without having these duplicates. Any advice or guidance would be greatly appreciated.

Thank you in advance for your help!

[amitchandak · Accepted solution]

@Anonymous , One of the solutions I can think of is to have the employee name/manager name in a disconnected table. Then you can filter the values selected in one or more column

example countrows(filter(Table, Table[Level1] in values(mgr[empname]) || Table[Level2] in values(mgr[empname]) ))

This is suitable when you have multiple columns, if you have only one column as manager id a duplicate table or inactive join  can help

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Also check, if needed

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

[mickey64 · Accepted solution]

Step 0: I use a DATA table below.

Step 1: I duplicate 3 column in Power Query Editor.

Before copy

After copy

Step 2: I unpivot these 3 columns.

Step 3: I click 'Close & Apply' and make a matrix and a slicer.

- Matrix -

- Slicer -

- Select 'Bob' -

[Anonymous]

Thank you very much!

[amitchandak · Accepted solution]

@Anonymous , One of the solutions I can think of is to have the employee name/manager name in a disconnected table. Then you can filter the values selected in one or more column

example countrows(filter(Table, Table[Level1] in values(mgr[empname]) || Table[Level2] in values(mgr[empname]) ))

This is suitable when you have multiple columns, if you have only one column as manager id a duplicate table or inactive join  can help

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Also check, if needed

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

[Anonymous]

Thanks!!