DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic sometimes. Especially one function, the Calculate function, can be challenging.
This is the first part of a series of blog posts, which will investigate the mysterious Calculate function.
The Calculate function reacts differently to row, column, filter, and table context to other DAX functions. We need it for nesting a number of DAX functions.
Examples of how the Calculate function works in Row Context
The examples will be based on sales data, and you can see the data model below.
In the first example the Customers need to be categorised by number of orders. Customers with more than 40 orders are A client and the rest are B clients.
In the example below an IF function has been used to manage the logic in a column calculation in the Customers table. The DAX looks like this:
Customer A and B clients = If(COUNTROWS(Orders)>40,”A Client”,”B Client”)
The logic is that every order has a unique record in the Orders table, but as you can see below, the DAX return that all the customers are A Clients. The DAX does not understand that each customer needs to be tested for more than 40 orders.
To get a better understanding of this the DAX has been changed in the example below to only calculate the number of orders (rows in the orders table).
The example shows 6037 for each customer. The result is not filtered by the relationship and do not display the number of orders for each customer. So in the IF logic example, you just need to test whether 6037 is greater than 40.
Communicating to Power BI about the data test
To communicate to Power BI that we want to test the number of orders for each row in the customers table (return the number of orders for each customer), the calculated column needs to be changed to: # of Orders = COUNTROWS(relatedtable(Orders))
The RelatedTable function will filter the number of orders by each customer and as you can see in the example below, the column will now show the result by customer, and not just the total number of orders.
To get the first example right the DAX also needs to be amended to:
Customer A and B clients = If(COUNTROWS(RELATEDTABLE( Orders))>40,”A Client”,”B Client”)
You can see in the result below, that now the DAX return the right categories.
So the examples show that when you do calculated columns, you will need the RelatedTable function to get the result for each row, but not if you use the mysterious Calculate function. The Calculate function is working with the row context completely different than the rest of the DAX functions.
In the example below the DAX has been changed to:
Customer A and B clients = calculate(If(COUNTROWS( Orders)>40,”A Client”,”B Client”))
The IF logic has been nested in a calculate function and the RelatedTable function had been removed. You can see that the amended calculation returns the right result.
Conclusion
I call the Calculate function the mother of DAX functions. It is the most important DAX function (my personal opinion), but to get the most out of it you will need to understand how the function reacts to row, column, filter, and table context.
In the next blog post in this series, you will see how the mysterious Calculate function is different from other DAX functions when it comes to filter context.