Filtering by slices of each other

[Anonymous]

I have 6 tables with unique values in my report, which are used for filtering in slicers and are connected to fact tables, there is also a SlicerConnectors table with unique combinations of values from tables with unique values, which is associated with these 6 tables. Is there a way to make the slicers filter each other? I.e. when selecting a value in one slicer, only the corresponding values should be displayed in the others. Initially, I wanted to do this by installing bidirectional cross-filtering in the links between SlicerConnectors and tables, but this option turned out to be non-working due to the ambiguity error of the filtering paths.

[AllisonKennedy · Accepted solution]

@Anonymous How many fact tables do you have?

You need to think about why you want the slicers to filter each other, and create that deliberately. So for example, let's say you have 2 fact tables:

Fact_Targets

Fact_Actuals

and your 6 dimension tables are:

Dim_Customer

Dim_Date

Dim_Region

Dim_Product

Dim_Promotion

Dim_Employee

Let's also assume that only 4 of your dimensions are connected to the Targets table:

Dim_Date

Dim_Region

Dim_Product

Dim_Employee

In order to enable the filtering from one dimension to another, we must use the fact tables that link them. To make this easy, I create a measure for each fact table:

CountRows_Targets = COUNTROWS(Fact_Targets)

CountRows_Actuals = COUNTROWS(Fact_Actuals)

then you can additionally create a measure that uses ALL fact tables:

CountRows_AllFacts = [CountRows_Targets] + [CountRows_Actuals]

When you select a Customer in a slicer, you'd possibly only want to see products that customer has actually bought. So in that case, you'd add a filter to the Product slicer as a visual level filter using the measure [CountRows_Actuals] >0 

However, by having only the Actuals filter the Product slicer, this means that when you select a Region from the Region slicer (region filters both Targets and Actuals), you'll only see products in the Product slicer that actually had sales in the selected region. You won't see products that had a Target but no Actual sales for that region. If that's your desired result, no changes needed. If not...

To fix this, use the [CountRows_AllFacts] >0 as a visual level filter on the Product slicer and remove the filter for [CountRows_Actuals] >0