topic Re: URGENT!! Measure that ignores slicers/ filters in DAX Commands and Tips

URGENT!! Measure that ignores slicers/ filters

Anonymous — Thu, 22 Feb 2024 08:38:17 GMT

Hello Power BI community,

Can you help me modify this code such that if I use SankeyTable'[Category_Planning] as a slicer in my power bi report and filter any one of its values, this measure should be able to ignore the SankeyTable'[Category_Planning] slicer and always keeps all the 4 values of SankeyTable'[Category_Planning] in the calculation?

MFelix , amitchandak , Ashish_Mathur

DistinctCount_Category_Occasions_MultiSelect_Part12 = VAR CategorySelection = ISFILTERED('df_Category Purchased'[Category Purchased]) VAR SelectedChannel = SELECTEDVALUE('df_Category Purchased'[Category Purchased]) RETURN IF ( CategorySelection, IF ( Not SelectedChannel IN { "JUICE", "SPORTS DRINKS", "PACKAGED WATER", "RTD TEA"}, (SUMX(VALUES(df_Barriers_SSD[Barriers_SSD]), CALCULATE( DISTINCTCOUNT('Respondent'[Unique_ID]), 'df_Occasions'[value1] = "Yes",'SankeyTable'[Category_Planning] IN {"Decided", "", "Open/Considered", "Not Answered"}, FILTER( 'Respondent', [IsBarriersSSD] ) ))), BLANK () ), (SUMX(VALUES(df_Barriers_SSD[Barriers_SSD]), CALCULATE( DISTINCTCOUNT('Respondent'[Unique_ID]), 'df_Occasions'[value1] = "Yes",'SankeyTable'[Category_Planning] IN {"Decided", "", "Open/Considered", "Not Answered"}, FILTER( 'Respondent', [IsBarriersSSD] ) ))) )

Re: URGENT!! Measure that ignores slicers/ filters

Sahir_Maharaj — Thu, 22 Feb 2024 08:46:49 GMT

Hello Anonymous,

Can you please try this:

DistinctCount_Category_Occasions_MultiSelect_Part12 = VAR CategorySelection = ISFILTERED('df_Category Purchased'[Category Purchased]) VAR SelectedChannel = SELECTEDVALUE('df_Category Purchased'[Category Purchased]) RETURN IF ( CategorySelection, IF ( NOT SelectedChannel IN { "JUICE", "SPORTS DRINKS", "PACKAGED WATER", "RTD TEA" }, SUMX( VALUES(df_Barriers_SSD[Barriers_SSD]), CALCULATE( DISTINCTCOUNT('Respondent'[Unique_ID]), 'df_Occasions'[value1] = "Yes", 'SankeyTable'[Category_Planning] IN { "Decided", "", "Open/Considered", "Not Answered" }, FILTER( ALL('SankeyTable'[Category_Planning]), // This line removes the slicer/filter effect on Category_Planning [IsBarriersSSD] ) ) ), BLANK() ), SUMX( VALUES(df_Barriers_SSD[Barriers_SSD]), CALCULATE( DISTINCTCOUNT('Respondent'[Unique_ID]), 'df_Occasions'[value1] = "Yes", 'SankeyTable'[Category_Planning] IN { "Decided", "", "Open/Considered", "Not Answered" }, FILTER( ALL('SankeyTable'[Category_Planning]), // This line removes the slicer/filter effect on Category_Planning [IsBarriersSSD] ) ) ) )

Re: URGENT!! Measure that ignores slicers/ filters

MFelix — Thu, 22 Feb 2024 19:12:19 GMT

Hi Anonymous

Try the following code:

DistinctCount_Category_Occasions_MultiSelect_Part12 = VAR CategorySelection = ISFILTERED ( 'df_Category Purchased'[Category Purchased] ) VAR SelectedChannel = SELECTEDVALUE ( 'df_Category Purchased'[Category Purchased] ) RETURN IF ( CategorySelection, IF ( NOT SelectedChannel IN { "JUICE", "SPORTS DRINKS", "PACKAGED WATER", "RTD TEA" }, ( SUMX ( VALUES ( df_Barriers_SSD[Barriers_SSD] ), CALCULATE ( DISTINCTCOUNT ( 'Respondent'[Unique_ID] ), 'df_Occasions'[value1] = "Yes", 'SankeyTable'[Category_Planning] IN { "Decided", "", "Open/Considered", "Not Answered" }, REMOVEFILTERS ( 'SankeyTable'[Category_Planning] ), FILTER ( 'Respondent', [IsBarriersSSD] ) ) ) ), BLANK () ), ( SUMX ( VALUES ( df_Barriers_SSD[Barriers_SSD] ), CALCULATE ( DISTINCTCOUNT ( 'Respondent'[Unique_ID] ), 'df_Occasions'[value1] = "Yes", 'SankeyTable'[Category_Planning] IN { "Decided", "", "Open/Considered", "Not Answered" }, REMOVEFILTERS ( 'SankeyTable'[Category_Planning] ), FILTER ( 'Respondent', [IsBarriersSSD] ) ) ) ) )