Dynamically filtering slicers involving a calculated table

[cyboysen]

Hi all, I'm a Power Bi noob and first time poster, so my apologies in advance for any forum faux pas.

I am attempting to create a visual in Power Bi with a bar graph and two slicers.  My primary data source is a table named tbl_MasterListComplete.  Here is some example data:

Hiring Class	Candidate Name	Interviewer A	Interviewer B	Completed Ramp Month 1	Completed Ramp Month 2	Completed Ramp Month 3
June 2022	Scooby	Brian	Kate	Yes	Yes	Yes
July 2023	Shaggy	Jane	John	Yes	Yes	No
July 2023	Velma	Zach	Jane	Yes	No	No
September 2024	Fred	Kate	Jane	No	No	No

I created measures in Power Bi to count the # of "Yes's" in each ramp month.  These are the data values for a bar graph.

The first slicer "Hiring Class" uses the Hiring Class column to select which hiring classes to display counts for.

ex. June 2022, July 2023, September 2024

I am trying to make a second slicer "Interviewer Name" that is a list of only the unique names from columns Interviewer A and Interviewer B:

ex. Brian, Jane, John, Kate, Zach

I want this list to dynamically adjust based on the Hiring Class selection.

ex. If only July 2023 is selected in the Hiring Class slicer, then the Interviewer Name slicer should only display Jane, John, & Zach, and the bar graph should only display counts for any selected interviewers for that hiring class.

I do not know how to accomplish this behavior.  I have made a calculated table "tbl_DistinctInterviewers" of all the distinct names from both Interviewer A/B columns, but I can't have 2 active relationships between tbl_MasterListComplete and tbl_DistinctInterviewers.

Any insight or recommendations would be greatly appreciated.  Thank you in advance!

[cyboysen]

Thank you so much for your advice, which was hugely helpful in putting me on the right track!  It took a little figuring out and tinkering around, but I got it working how I wanted it to!  You're #1!

[lbendlin · Accepted solution]

I want this list to dynamically adjust based on the Hiring Class selection.

Yeah, this is where it gets tricky.  What you will need to do is precompute a reference list with all distinct Interviewer names. This has to be a column, it cannot be a measure.  Measure cannot feed slicers. (Measures can be used as visual filters, though, so you can still achieve your goal.

This reference table needs to be disconnected from the data model. Then use the measure to read the selected values from the slicer, and check if the current row of the visual contains one of the values in either of the two interviewer columns. If it does, let the measure return 1, else 0.  Then filter the visual by the measure value = 1.