How to pass Filters in Measures using DAX with various methods in Power BI

How to pass Filters in Measures with various methods using DAX in Power BI

Scenario:

Suppose we have a Data Model as per below:

Based on the above Model, the No. of Units Sold by Month-Year and Country is as follows:

Units Sold = SUM(tbl_Sales[Units_Sold])

Now we look at how to pass the below Filters using various methods to calculate the Units Sold.

DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}

tbl_Region[Country_Name] IN {"France", "Japan"}

The expected Result for with above Filters = (290 + 279) = 569

Below method will ignore ALL external filters from the DimCalendar and tbl_Region tables, and apply only the implicit filters passed within the expression:

Frn_Jpn Q1 Sales (Filter ALL Method) =

CALCULATE( [Units Sold],  

    FILTER( ALL(DimCalendar) ,             DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}             ),

    FILTER( ALL(tbl_Region) , tbl_Region[Country_Name] IN {"France", "Japan"})

    )

Below method will apply the implicit filters directly with in the CALCULATE, without clearing external filters from DimCalendar or tbl_Region:

Frn_Jpn Q1 Sales (Direct Filter Method) =

CALCULATE( [Units Sold],  

    DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"},

    tbl_Region[Country_Name] IN {"France", "Japan"}

    )

Below method will apply the implicit filters using FILTER() on DimCalendar and tbl_Region, and is impacted by external filters from any slicers used in the data model:

Frn_Jpn Q1 Sales (Filter Method) =

CALCULATE( [Units Sold],  

    FILTER(DimCalendar,

        DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}

        ),

    FILTER(tbl_Region,

        tbl_Region[Country_Name] IN {"France", "Japan"}

        )

    )

Below method will apply the implicit filters using VALUES() from DimCalendar[MonthYear] and tbl_Region[Country_Name], so the calculation is impacted by external filters from slicers in the data model:

Frn_Jpn Q1 Sales (Values Method) =

CALCULATE( [Units Sold],  

    FILTER(VALUES(DimCalendar[MonthYear]),

        DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}

        ),

    FILTER(VALUES(tbl_Region[Country_Name]),

        tbl_Region[Country_Name] IN {"France", "Japan"}

        )

    )

Below method will apply the implicit filters using TREATAS() to map a virtual table of values onto DimCalendar[Qtr_Yr] and tbl_Region[Country_Name], while preserving external filters with KEEPFILTERS:

Frn_Jpn Q1 Sales (TreatAs Method) =

CALCULATE( [Units Sold],

    KEEPFILTERS(

        TREATAS(

            {   ("Q1-2022", "France"),

                ("Q1-2022", "Japan")

            },

            DimCalendar[Qtr_Yr],

            tbl_Region[Country_Name]

            )

        )

    )

Below method will apply the implicit filters using tuple-based conditions (IN {…}) across multiple columns, combined with KEEPFILTERS, so external slicers continue to influence the calculation:

Frn_Jpn Q1 Sales (Tuples Method) =

CALCULATE( [Units Sold],

    KEEPFILTERS(

        ( tbl_Region[Region_Name],tbl_Region[Country_Name] )

        IN {   ("Europe","France"),("Asia","Japan")}

        ),

    KEEPFILTERS(

        ( DimCalendar[MonthYear] )

        IN {   ("Jan-2022"),("Feb-2022"),("Mar-2022") }

        )

    ) Result:

Notes: Please make the required adjustments to the Filter Context in the expressions based on your Scenario and Data Model.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------