CALCULATE Function: March 2021 Power BI DAX Update

Recently in March 2021 we got an update in Power BI regarding usage of the CALCULATE function. This update has improved writing a smaller DAX with multiple OR conditions coming from the same table. In this blog, I have tried to explain how this has improved and in what scenarios. I want to thank Parry for giving me an inspiration on writing this blog and helping me understand the key differences on this update when compared to the last Power BI Desktop update. 

I have a simple data model with just 2 tables and the following relationship between them:

1.      Clothing Sales table – data regarding the sales for different clothing categories.

2.      Calendar table – it is a date table in my data model.

Let us also consider the different scenarios that we will go through in this blog to check the different conditional statements for CALCULATE function. The only reason to adapt multiple scenario approach is to help with the understanding of the DAX expressions in various ways:

1. OR conditions

• Same table (this scenario is about this month’s update)
• Different table

2.  AND conditions

• Same table
• Different table

1a Scenario - Multiple OR conditions from Same Table:

Power BI Old Version:

In the previous version of Power BI, if we write the following measure with multiple OR conditions coming from the same table to get the total sales by category and by date, then we would have ended up with the following error:

Power BI March 2021 Version:

In the latest version, we do not get this error for the same measure which gave an error in the older version:

This is what is improved in the recent update for adding multiple OR conditions coming from the same table in the DAX expression using CALCULATE function.

1b Scenario - Multiple OR conditions from Different Tables:

In the current and previous versions of Power BI, if we write the following measure with multiple OR conditions coming from two different tables, then we will end up with the following error:

This error still exists in current Power BI update. To avoid this error, one way we can use to modify the DAX is shown below: (there can be other ways as well to achieve this)

2a Scenario - Multiple AND conditions from Same Table:

Using multiple AND conditions has always worked in Power BI when they come from the same table when following the best practices for writing the DAX expression.

In the current and previous versions of Power BI, we can write the following measure with multiple AND conditions as follows:

2b Scenario - Multiple AND conditions from Different Tables:

Using multiple conditions with AND clause has never worked in Power BI directly when coming from different tables along with the filter contexts.

But we can fix this error by again writing the DAX expression as follows to get the right results:

Let us conclude the blog with few key points:

• March 2021 update is focused around improving writing the DAX expression using CALCULATE with Multiple OR conditions coming from Same table.
• Multiple OR conditions coming from different tables has no impact from this month’s update.
• There is no impact on multiple AND conditions coming from Same or Different tables.

This is just a small attempt to explain the understanding around what this update is and what it includes. Look forward to the feedback.

Pragati