Stop interaction between a slicer and a measure

[souvik900766]

Hi !

I want to stop interaction between a slicer and a measure.
I want to calculate a KPI where the numerator and the denominator will be filtered by two different slicers, but both slicers contains brands, one slicer for a specific manufaturer and other slicer for all manufacturers
First slicer will only filter the numerator and the second slicer will only filter the denominator .
PFA image, data and the dashboard.
Link to data:-https://www.dropbox.com/sh/zwl481drh2zn7mz/AAA3FDfcBfw5aBGDNiULqm4ka?dl=0

Thanks in advance !

[Ashish_Mathur]

Hi,

What is the end result you are expecting?

[souvik900766]

Hi @Ashish_Mathur ,
The end result I am expecting is that both measures should stay in the table whenever I filter both the slicers of brand name simultaneously. I want to calculate INDEX that will be a numerator which will be filtered by Brand ABC slicer and a denominator which will be filtered by Brand DEF slicer and based on the filter after division I will get the INDEX result.
Brand ABC (i.e the slicer that will filter numerator) has definite sets of brands belonging to a particular Company and Brand DEF (i.e the slicer that will filter denominator) has all companies except the Brand ABC company.
Currently, whennever I am selecting both from ABC slicer and DEF slicer one of the measure is going of .
So, I want the solution to keep both measures which will be filtered on respective slicers selected.

[AlexisOlson · Accepted solution]

It sounds like you don't actually want the slicers to filter your data directly (since the selections are mutually exclusive resulting in an empty set) but they do need to affect your measure(s).

In this sort of situation, you need a disconnected table to use for the slicer(s). You can define calculated tables like this:

BrandABC = SUMMARIZE( FILTER ( MSR, MSR[company_nm] = "ABC Company" ), MSR[Brnd-nom] )

BrandDEF = SUMMARIZE( FILTER ( MSR, MSR[company_nm] = "DEF Company" ), MSR[Brnd-nom] )

 and then define measures to read selections based on these tables.

Value for ABC =
VAR BrandABC = SELECTEDVALUE ( BrandABC[Brnd-nom] )
RETURN
    CALCULATE ( SUM ( MSR[val] ), MSR[Brnd-nom] = BrandABC )

Value for DEF =
VAR BrandDEF = SELECTEDVALUE ( BrandDEF[Brnd-nom] )
RETURN
    CALCULATE ( SUM ( MSR[val] ), MSR[Brnd-nom] = BrandDEF )

This assumes you have your slicers set up for BrandABC[Brnd-nom] and BrandDEF[Brnd-nom] with a single brand selected for both. If you want to allow for multiple selections, replace SELECTEDVALUE with VALUES and write "IN BrandABC" instead of "= BrandABC"