Effective March update of Power BI you can specify different columns in the single filter argument of CALCULATE without writing the equivalent expanded version.
Earlier you had to write:
Red Contoso =
CALCULATE (
[Total Sales],
Products[Color] = "Red",
Products[Brand] = "Contoso"
)
Because Products[Color] = “Red” and Products[Brand] = “Contoso” internally expands to the below version, therefore they couldn’t be combined into one filter (row context mapping issue):
FILTER (
ALL ( Products[Color] ),
Products[Color] = "Red"
)
FILTER (
ALL ( Products[Brand] ),
Products[Brand] = "Contoso"
)
And to write them in a single filter we had to write
Red Contoso =
CALCULATE (
[Total Sales],
FILTER (
ALL ( Products[Color], Products[Brand] ),
Products[Color] = "Red"
&& Products[Brand] = "Contoso"
)
)
With the March update you can write:
Red Contoso =
CALCULATE (
[Total Sales],
Products[Color] = "Red"
&& Products[Brand] = "Contoso"
)
And that internally expands into:
Red Contoso =
CALCULATE (
[Total Sales],
FILTER (
ALL ( Products[Color], Products[Brand] ),
Products[Color] = "Red"
&& Products[Brand] = "Contoso"
)
)
The new version makes code a little bit less verbose, earlier if you wanted to ensure that the Filters inside CALCULATE don’t overwrite the existing filters you had to write:
Red Contoso =
CALCULATE (
[Total Sales],
KEEPFILTERS ( Products[Color] = "Red" ),
KEEPFILTERS ( Products[Brand] = "Contoso" )
)
or
Red Contoso =
CALCULATE (
[Total Sales],
KEEPFILTERS (
FILTER (
ALL ( Products[Color], Products[Brand] ),
Products[Color] = "Red"
&& Products[Brand] = "Contoso"
)
)
)
Now you can write more compact version:
Red Contoso =
CALCULATE (
[Total Sales],
KEEPFILTERS ( Products[Color] = "Red"
&& Products[Brand] = "Contoso" )
)
Internal expansion is visible in the logical query plan:
Read:
