Unexpected filter behavior in CALCULATE

Solution: Unexpected filter behavior in CALCULATE

Filters in CALCULATE override previously existing filters on the column they use. For example, CALCULATE ( [Sales], Product[Color] = "Red" ) returns red sales, no matter what you filter in a pivot table. Nevertheless, this golden rule sometimes looks not to work as expected, and it is time for another puzzle! Hurry up, this puzzle has a limited lifetime, since it depends from a behavior that the team is already fixing, it might be outdated in a few weeks!

The problem is the sort by column feature and the fact that Power BI uses DAX to query the data model. When you use the “sort by column” property, the report includes both the column (in our case the month) and the sorter (in our case the month number) in the groupby parameters of SUMMARIZE when it queries the tables. The report produces a query similar to this:

ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        Date[Month],
        Date[Month Number]
    ) ,
    "SumOfSales", [SumOfSales],
    "SalesOfMarch", [SalesOfMarch]
)
ORDER BY [Month Number]

If you expand the measures, this is the resulting query.

ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        Date[Month],
        Date[Month Number]
    ) ,
    "SumOfSales", CALCULATE ( 
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) 
    ),
    "SalesOfMarch", CALCULATE ( 
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), 
        'Date'[Month] = "March" ) 
    )
)
ORDER BY [Month Number]

The filter applied to the Month column in the SalesOfMarch calculation overrides correctly the month name, but the context transition performed by CALCULATE contains a filter on the month number too, and the expression does not remove that filter. Thus, for all the months (apart from March) the intersection of the filters will result in an empty set, making only the combination (Month=March, Month Number=3) survive the filtering.

To make it working, you have to define SalesOfMarch with an explicit ALL on the month number:

Sales[SalesOfMarch] := 
CALCULATE ( 
    [SumOfSales], 
    'Date'[Month] = "March", 
    ALL ( 'Date'[Month Number] ) 
)

The issue is not present in Excel because it uses MDX to query the model. MDX automatically handles the sorting based on model’s metadata, so the query does not have to specify the month number among the groupby columns.

Download