Blog

Our trainers in Power BI DAX training in Australia dedicate a major part of training session to teach the CALCULATE() function which has the power of context transition. Context transition is the conversion of row context into a filter context using the CALCULATE() or CALCULATETABLE() functions. Understanding and using the context transition is only possible if you have a good knowledge of evaluation contexts, i.e., row and filter contexts in DAX.

We recommend that our readers go through the following blog before understanding context transition:

• Row context and filter context: What is Evaluation Context in DAX? – Power BI Training Australia

To find number of distinct products bought by a customer:

1. Right click on the Customer table in the Fields
2. Click on New column.

Column tools open.

3. Type the following formula in the formula bar and press Enter.

In this Expression:

• The unique product keys from the Sales table are counted.

4. Click on Data tab in the left Ribbon.
5. Click on Customer table in the Fields
6. Click on the drop box at the top on Number of Products Purchased (CC).
7. Click on Sort Descending.

The result is not correct because all of the customers cannot buy the same number of products. However, CALCULATE() allows us to get the result. Calculate converts Row context into filter context. This is called context transition. CALCULATE() will be discussed in more detail later in the course.

8. Click on the Number of Products Purchased (CC)

Column tool box appears containing the formula bar.

9. Add CALCULATE() before the DISTINTCOUNT() in the formula and press Enter.

Your DAX expression should look similar to the expression below.

In this Expression:

• The filter context is modified by CALCULATE().

10. Click on the drop box at the top on Number of Products Purchased (CC).
11. Click on Sort Descending.

In the example below, you can see Nancy Chapman had 57 sales but only bought 22 different products.

Last Order Date of a Customer:

To find last order date by a customer using context transition:

12. Right click on the Customer table in the Fields
13. Click on New column.

Column tools open.

14. Type the following formula in the formula bar and press Enter.

In this Expression:

• The MAX() function finds the latest order date in the Sales Table.
• CALCULATE() modifies the filter context so that the last order date for each customer can be filtered.

15. Format the Last Order Date (CC) column to (m/d/yy).
16. Click on Data tab in the left Ribbon.
17. Click on Customer table in the Fields

Notice that Nancy last ordered on 12^th of June, 2020.

Conclusion:

Context transition is an important concept in DAX. This concept is based on the power of CALCULATE() function. Context transition refers to conversion of row context into filter context using CALCULATE(). In this blog, a common business problem of finding the number products purchased by the customer and the last date of purchase by the customer is solved using the power of CALCULATE().

Learning Power BI from Scratch? Get a complete, in-depth training on Microsoft Power BI by attending our training courses.

Power BI Training in Sydney

Power BI Training in Melbourne

Power BI Training in Brisbane