DAX | CALCULATE | March’ 2021 Update
Updated: Apr 8
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:
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):
And to write them in a single filter we had to write
With the March update you can write:
And that internally expands into:
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:
Now you can write more compact version:
Internal expansion is visible in the logical query plan:
The new way of writing filters in CALCULATE definitely improves the readability but does it improve the performance as well?
Sometimes writing filters separately helps in improving the performance as the Engine doesn't have to do a single scan for tuples satisfying the condtions and in some cases 2 scans are better than a single scan. I Will keep you posted once I find something worth sharing!