URGENT!! Measure that ignores slicers/ filters

[Anonymous]

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]
                )
            )))
    )

[MFelix · Accepted solution]

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] )
                )
            )
        )
    )

[Sahir_Maharaj]

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]
                )
            )
        )
    )