Filter Multiple Columns by Value of another

[Anonymous]

I have a sales dashboard where I want to filter the information based on teams managed by individuals. The only challenge is that I want this to be filterable not only by the top level manager, but also by sub-managers.

My data model is set out so that I have a table that looks something like the below (made up names and there is more to the actual table e.g. an employee_id that is used to filter down the sales table):

Employee	Manager	2nd Manager	3rd Manager
John Smith	Jane Doe	Elliot Buckle
Elliot Buckle
Jane Doe	Elliot Buckle
Jemima Snow	Jane Doe	Elliot Buckle
Jarrod McCarthy	Jemima Snow	Jane Doe	Elliot Buckle

I would like to be able to filter by the employee in the first column, and for it to filter for the employees where the selection appears in any of the 4 columns.

E.g. if I select "Elliot Buckle" it would filter for all employees (As Elliot Buckle appears in one of the columns for all rows)

where-as if I select Jemima Snow it would filter for the employees "Jemima Snow" and "Jarrod McCarthy" as they are the only two that are referenced.

Ideally this would be done through an employee slicer on the dashboard (e.g. a dropdown menu containing the employee names in the first column), but after searching and playing around I havent been able to make something like this as of yet.

If anyone can assist with ideas for solutions that would be amazing!

[Anonymous · Accepted solution]

Hi @Anonymous ,

The best potential solution is to set RLS in Power BI. Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. 

Row Level Security Configuration in Power BI Desktop - RADACAD

What Do You Need to Implement Dynamic Row-Level Security in Power BI? - RADACAD

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , I think you need inactive joins and userelationship.

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

Or employee table without join to this table and a measure like

measure =

var _tab  = allselected(Employee[Employee])

return

countrows(filter(Table, Table[Employee] in _tab || [Manager] in _tab || [2nd Manager] in _tab || [3rd Manager] in _tab ))