Category: Hints & Tips

Hints and tips for MS Office and other applications. Contributions from our team of talented trainers.

Posted on

Power BI’s Mysterious Calculate Function 2: Filter Context

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.

calculate

 

This is the second part of a series of blog posts, which will investigate the mysterious Calculate function.

It reacts differently to row, column, filter, and table context than the other DAX functions, and will be needed for nesting a number of DAX functions.

In this blog post you will see some examples of how the calculate function works in filter context.

Matrix visual

In the example below, you can see sales in a matrix visual. The visual displays total sales for each sales rep, and sales for a specific product. In this example, backpacks are the product.

In the example the sales have been filtered and calculated by using the SUMX function.

Sales of Backpack = sumx(filter(Line_Items,RELATED(Items[Product Description])=”backpack”),[sales])

 

To do it with the SUMX function, you need to use the FILTER function. We need a Related function to reference the column in the Items table, and the expression is the measured total sales.

 

calculate

 

Now Using the Calculate Function

Now let us see how to do the same, but this time with the mysterious Calculate function.

Calc Sales Backpack = CALCULATE([sales],Items[Product Description]=”backpack”)

 

You can now do it with only one function – the Calculate function. This means that you will not need to use the Filter function to filter by the product, and you will no longer need to use the Related function.

See the matrix below. The Calculate measure returns exactly the same as the SUMX measure.

calculate

But the mysterious Calculate function can behave may be a way which seems illogical.

Below the matrix has been amended to display sales by product instead of sales rep.

calculate

 

Now the Calculate measure, as you can see above, cannot understand that it needs to filter the table by each product. The SUMX measure can. The Calculate measure just shows the same sales of backpack total for all the products, but it does not sum up all the values in the Total row at the bottom of the matrix.

To get the Calculate function to return the right result, a Filter and a Related function are needed.

Calc Sales Backpack = CALCULATE([sales],filter(Line_Items,RELATED(Items[Product Description])=”backpack”))

 

Above you can see that the amendment of the Calculate function now shows the expected result.

But can this special behaviour be useful?

You could be in a situation, where you want to compare the sales of backpack up against the other products. Following on, the next example shows a percentage difference of backpack sales versus other products which needs to be visualised.

Backpack % of other products = DIVIDE([sales],CALCULATE([sales],Items[Product Description]=”backpack”),0)

 

The measure above will do the job. The Calculate measure without the Filter function or the Related function will return the total sales of backpack in each other product’s rows in the matrix. So that sales for each product will be measured against the sales of backpacks.

Above you can see the result of this approach, and of course the percentage difference from backpack and backpack will return 100%.

Conclusion

I call the Calculate function the mother of all 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 function is needed for use of date intelligence in Power BI desktop measures.

Posted on

Power BI’s Mysterious Calculate Function: 1 – Row Context

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.

data

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.

data

 

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.

data

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.