Slicer selection to limit what's shown in other slicers

[jscottNRG]

Apologies if this is already well-documented elsewhere, but I was unable to find the answer to my problem.

My data model is more complicated, but I think I can simplify to illustrate my issue.  I have three fact tables (Job_info, RoughInspections_merged and FinalInspections_merged) and three dimension tables (dim_jobstatus, dim_supervisors and Calendar) that drive my slicers.  "dim_jobstatus" is small, with just two rows, while "dim_supervisors" has over 700, and Calendar has dates for the last two years.

I'm still new to Power BI, DAX and the data models generally and recently learned that I need to use dimension tables for slicers, not columns from fact tables.  However, my previous setup meant that when I made a slicer selection, say in dim_jobstatus, the slicers that use Calendar and dim_supervisors showed only a relevant subset.  In other words, if I picked "Job Status"="Complete" in a slicer, my Supervisor slicer would only show Supervisors with Complete jobs. 

Now, using the dimension tables with relationships set to Single direction, when I make a selection on one slicer the other slicers aren't updated.  This means if I pick "Complete" for Job Status, "John Doe" will still show up in the Supervisor slicer despite the fact that John Doe doesn't have any Complete jobs.

Thoughts for solving?  Or is this just the accepted way that slicers work together?  Thanks in advance for help.

Jonathan

[Anonymous · Accepted solution]

Hi @jscottNRG,

According to your snapshot, when you filter on slicer, it will use filtered content to hide unmatched records, then use remained records to filter other table through relationship columns.(this is why 'John Doe' who not has completed jobs and still displayed)

I'd like to suggest you modify relationships to use dim table as bridge table to link other tables and job_info table.(setting cross filter direction to 'both')

DAX Puzzle: Multiple many-to-many relationships

Regards,

Xiaoxin Sheng

[jscottNRG]

Hi @Anonymous, thanks for your reply.  Solving the issue was a simple as changing the dim tables to job_info relationships to 'both.'  I was advised that I should avoid setting relationship cross filtering to 'both' as it can meaningfully affect report performance, but in this case it looks like that's what I need to do.

I appreciate the help!