How to Apply Slicer to Multiple Fields in Table

[Anonymous]

Hello,

I'm trying to build a report with a slicer that will filter values based on multiple fields in a table. I have a base table with store information including Employee information in columns Manager, Shift 1, and Shift 2. I have a seperate table (Employee Table), which contains a list of all employees. I would like to be able to use a slicer using Employee Name from the Employee Table so that a text table will filter store records based on whether the selected Employee Name is either in the Manager, Shift 1, and Shift 2 fields.

So for the example below: I would like to filter to show all store records where Dana is either in the Manager, Shift 1, or Shift 2 field. This means the table would only show the highlighted stores (K, D, J, C, B, I).

Report View

I'm having difficulty understanding how best to integrate this functionality into the data structure & report. There are multiple other tables that need to be joined to the base table as well (see below).

Model View

Any assistance with finding a solution for this is greatly appreciated. Sample data can be found below.

Thank you,

Paul

Base Table

Store	Manager	Shift 1	Shift 2
A	Katlyn	Rob	Evelyn
B	Rob	Evelyn	Dana
C	Evelyn	Dana	Michael
D	Dana	Michael	Nick
E	Michael	Nick	Tom
F	Nick	Tom	Katlyn
G	Tom	Katlyn	Rob
H	Katlyn	Rob	Evelyn
I	Rob	Evelyn	Dana
J	Evelyn	Dana	Katlyn
K	Dana	Katlyn	Rob

Employee Table

Employee Name

Katlyn

Rob

Evelyn

Dana

Michael

Nick

Tom

Katlyn

Rob

Evelyn

Dana

Store Location Table

Store	State
A	Texas
B	New York
C	California
D	New York
E	California
F	Texas
G	New York
H	California
I	New York
J	California
K	California

Store City Table

Store	City
A	Austin
B	New York
C	Los Angeles
D	New York
E	Los Angeles
F	Austin
G	New York
H	Los Angeles
I	New York
J	Los Angeles
K	Los Angeles

Store Hours Table

Store	Start Time	End Time
A	9:00	22:00
B	8:00	21:00
C	7:00	20:00
D	9:00	22:00
E	8:00	21:00
F	7:00	20:00
G	9:00	22:00
H	8:00	21:00
I	7:00	20:00
J	9:00	22:00
K	8:00	21:00

[amitchandak]

@Anonymous , one of the way is to create measure like

countrows(filter('Base Table', 'Base Table'[manager] in values(employee[employee name])  || 'Base Table'[Shift1] in values(employee[employee name]) || 'Base Table'[Shift2] in values(employee[employee name])  ))

another one is to create active inactive link and use userelationship

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

[Anonymous]

Thank you for the sample explanations. Can you elaborate on how the active inactive link would work in a slicer? I'm struggling to understand how it would work to filter records as opposed to using it to calculate a sum like the example on the website. Thank you