2 slicers to be combined with OR logic

[Meena0155]

Hello,

I have a requirement to have 2 slicers at a page level and the visuals on the pages has to be based on the condition - slicer 1 or slicer 2.

For eg, if in the first slicer(City) the user chose Sydney and for the second slicer (Country) if the user chose New Zealand, the visuals in the page should show records with a city of Sydney OR country of New Zealand.

The visuals on the page is based on 2 fact tables each having a many to 1 relationship with the city and country master tables. The visuals include a collection of scorecards and tables. 

Has anyone successfully managed to do this?  @Bibiano_Geraldo , @danextian 

Regards,

Meena

[lbendlin · Accepted solution]

You need to use disconnected tables to feed your slicers, and then you need to create a measure that can act as a visual filter based on your logic.

[danextian · Accepted solution]

hI @Meena0155 

You will need to use a disconnected table for each dimension table that the filters are coming from and use a measure returning a value that meets either of the selections. Please see attached sample pbix.

[Bibiano_Geraldo · Accepted solution]

Hi @Meena0155 ,

As suggested by @danextian and @lbendlin, you need to disconnect the city and country tables from your fact tables. This means removing the relationships between them. If you still need these relationships for other calculations in your report, you will need to duplicate the city and country tables. Ensure that the duplicated tables are not related to your fact tables.

With that clarified, let’s move on. Consider the following model: we have three tables — the Financials table (a fact table), and the Segment table (consider this as “city” in your model) and the Country table (dimension tables). None of these tables are related.

Create the following measure, replacing the table and column names with those in your model:

Measure = 
VAR vSelectedCountry = SELECTEDVALUE(Country[Country]) // From the Country dimension table
VAR vSelectedSegment = SELECTEDVALUE(Segment[Segment]) // From the Segment (City) dimension table

RETURN
IF(
    SELECTEDVALUE(Financials[Country]) = vSelectedCountry ||
    SELECTEDVALUE(Financials[Segment]) = vSelectedSegment,
    1,
    0
)

Step 2: Create the Desired Visual

Add a table visual:

• Include the Segment, Country, and Sales Amount columns (or equivalents in your model), all coming from the Financials fact table.

Set up a visual-level filter:

• Add the created measure as a visual-level filter.
• Configure the filter to show items where the value equals 1.

Now you can add two slicers to the report:

• A slicer with the Country column from the Country dimension table.
• A slicer with the Segment (City) column from the Segment dimension table.

Once the slicers are applied, the table visual will only display rows that match the selected criteria, filtered by the measure.

 

[v-stephen-msft]

Hi @Meena0155 ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[Bibiano_Geraldo · Accepted solution]

Hi @Meena0155 ,

As suggested by @danextian and @lbendlin, you need to disconnect the city and country tables from your fact tables. This means removing the relationships between them. If you still need these relationships for other calculations in your report, you will need to duplicate the city and country tables. Ensure that the duplicated tables are not related to your fact tables.

With that clarified, let’s move on. Consider the following model: we have three tables — the Financials table (a fact table), and the Segment table (consider this as “city” in your model) and the Country table (dimension tables). None of these tables are related.

Create the following measure, replacing the table and column names with those in your model:

Measure = 
VAR vSelectedCountry = SELECTEDVALUE(Country[Country]) // From the Country dimension table
VAR vSelectedSegment = SELECTEDVALUE(Segment[Segment]) // From the Segment (City) dimension table

RETURN
IF(
    SELECTEDVALUE(Financials[Country]) = vSelectedCountry ||
    SELECTEDVALUE(Financials[Segment]) = vSelectedSegment,
    1,
    0
)

Step 2: Create the Desired Visual

Add a table visual:

• Include the Segment, Country, and Sales Amount columns (or equivalents in your model), all coming from the Financials fact table.

Set up a visual-level filter:

• Add the created measure as a visual-level filter.
• Configure the filter to show items where the value equals 1.

Now you can add two slicers to the report:

• A slicer with the Country column from the Country dimension table.
• A slicer with the Segment (City) column from the Segment dimension table.

Once the slicers are applied, the table visual will only display rows that match the selected criteria, filtered by the measure.

 

[Meena0155]

Thanks @Bibiano_Geraldo ..

This solution works great with tables. But what about score_cards? I am not able to add a measure filter on the score card. is there anything we can do to make that happen?

Regards,

Meena

[Bibiano_Geraldo · Accepted solution]

As mentioned by @danextian , you will need to validate this directly in your measure.
Lets suppose that you have a measure like this:

Sales Amount = SUM(financials[ Sales])

You have to modify the measure to something like this:

Sales Amount = 
CALCULATE(
    SUM(financials[ Sales]),
    FILTER(
        financials,
        financials[Measure] = 1 --this is the measure we created before to filter in visual level
    )
)

[danextian]

For cards, you will need to have the filter in the measures themselves. There are no rows in KPIs so you cannot use the visual filter to filter each row. My previous reply has a sample pbix.

[danextian · Accepted solution]

hI @Meena0155 

You will need to use a disconnected table for each dimension table that the filters are coming from and use a measure returning a value that meets either of the selections. Please see attached sample pbix.

[Bibiano_Geraldo]

Hi @Meena0155, can you please share a file with no sensitive information just to see this closer?

[lbendlin · Accepted solution]

You need to use disconnected tables to feed your slicers, and then you need to create a measure that can act as a visual filter based on your logic.