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

The below 2 Methods, will ignore ALL (external) Filters from the DimCalendar and tbl_Region tables, and apply the implicit filters passed with in the expression:

Frn_Jpn Q1 Sales (Filter ALL Method1) =

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"})

    )

Frn_Jpn Q1 Sales (Filter ALL Method2) =

CALCULATE( [Units Sold],  

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

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

    )

The below Methods, impact by (external) Filters from any Slicers used from 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"}

        )

    )

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"}

        )

    )

Frn_Jpn Q1 Sales (TreatAs Method) =

CALCULATE( [Units Sold],

    KEEPFILTERS(

        TREATAS(

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

                ("Q1-2022", "Japan")

            },

            DimCalendar[Qtr_Yr],

            tbl_Region[Country_Name]

            )

        )

    )

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

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