Forum Discussion
Context Transition in DAX
Hello,
I'm currently watching this video about Context Transition in DAX. Unfortunately, my English and my current DAX knowledge are still quite limited, so I'm struggling to fully understand a few points. I hope someone can help me.
I downloaded the file "C0247 - Context transition in DAX explained visually.pbix", which is available in the video's description.
To better understand the example, I recreated part of the model in Excel and built a PivotTable. There are two measures in particular that I don't fully understand.
Measure 1:
Average Customer Sales = AVERAGEX ( Customer, [Sales Amount] )
In my PivotTable, the rows are based on the Brand field, while the measure iterates over the Customer table.
When I double-click the cell with the value 98.58 (the Tailspin Toys row), what is the exact calculation that DAX performs to arrive at this result?
Also, how should this measure be interpreted? If you had to give it a more descriptive name than Average Customer Sales, what would you call it?
Measure 2:
Average Product Sales = AVERAGEX ( 'Product', [Sales Amount] )
I have the same questions about the value 553.56
What calculation does DAX perform to produce this result?
If you were to give this measure a more descriptive name, what would it be?
Finally, why is the 'Product' table enclosed in single quotes, whereas Customer is not?
Thank you very much for your time and your help!
Best regards
Hi Mederic,
Average Customer Sales = AVERAGEX(Customer, [Sales Amount]) iterates over the Customer table one row at a time. For each customer, DAX evaluates [Sales Amount]. Because [Sales Amount] is a measure, it has an implicit CALCULATE around it, which triggers context transition. The current customer row becomes a filter, so [Sales Amount] returns only that customer's sales. The Brand = Tailspin Toys filter from your pivot is still active, so DAX sums only Tailspin Toys sales for that one customer.
The 98.58 in the Tailspin Toys row equals the total Tailspin Toys sales divided by the number of customers who actually bought Tailspin Toys. AVERAGEX ignores blank iteration results, so customers with no Tailspin Toys sales are not counted in the denominator. A more descriptive name would be "Sales per Buying Customer" or "Average Sales per Customer".
Measure 2 works the same way but iterates Product instead of Customer, so 553.56 is the average sales per product that had sales in the current filter context. A better name is "Average Sales per Product".
On the quotes: DAX requires single quotes around a table name when the name contains spaces, starts with a digit, or matches a DAX reserved word or function name. Product collides with the PRODUCT function, so 'Product' has to be quoted. Customer has no such collision, so it can be written unquoted.
If this helped clarify things, please give it a kudos and mark it as the accepted solution.
Best regards,
Shai Karmani
4 Replies
Hi Mederic,
Average Customer Sales = AVERAGEX(Customer, [Sales Amount]) iterates over the Customer table one row at a time. For each customer, DAX evaluates [Sales Amount]. Because [Sales Amount] is a measure, it has an implicit CALCULATE around it, which triggers context transition. The current customer row becomes a filter, so [Sales Amount] returns only that customer's sales. The Brand = Tailspin Toys filter from your pivot is still active, so DAX sums only Tailspin Toys sales for that one customer.
The 98.58 in the Tailspin Toys row equals the total Tailspin Toys sales divided by the number of customers who actually bought Tailspin Toys. AVERAGEX ignores blank iteration results, so customers with no Tailspin Toys sales are not counted in the denominator. A more descriptive name would be "Sales per Buying Customer" or "Average Sales per Customer".
Measure 2 works the same way but iterates Product instead of Customer, so 553.56 is the average sales per product that had sales in the current filter context. A better name is "Average Sales per Product".
On the quotes: DAX requires single quotes around a table name when the name contains spaces, starts with a digit, or matches a DAX reserved word or function name. Product collides with the PRODUCT function, so 'Product' has to be quoted. Customer has no such collision, so it can be written unquoted.
If this helped clarify things, please give it a kudos and mark it as the accepted solution.
Best regards,
Shai KarmaniHi Mederic
Great question! This is exactly how Context Transition works. Let's break it down with your Tailspin Toys = 98.58 example.
Measure 1: Average Customer Sale
`AVERAGEX ( Customer, [Sales Amount] )`
What happens for Tailspin Toys row = 98.58:
Context Transition means: For each Brand in the row, DAX automatically converts the row context into a filter context.So for "Tailspin Toys":
1. DAX takes the current Brand = "Tailspin Toys" from the PivotTable row
2. It applies that as a filter to the whole model
3. Then it runs AVERAGEX: It goes to the `Customer` table and loops through every Customer that bought Tailspin Toys
4. For each Customer, it calculates [Sales Amount] for only Tailspin Toys
5. Then it takes the AVERAGE of all those customer totalsExample Calculation:
Let's say Tailspin Toys was bought by 3 customers:
Customer A: 120.00
Customer B: 80.00
Customer C: 95.74
Average = (120 + 80 + 95.74) / 3 = 98.58Better name : `Avg Sales per Customer by Brand`
Measure 2: Average Product Sales
`AVERAGEX ( 'Product', [Sales Amount] )`What happens for Tailspin Toys row = 553.56:
Same logic, but now it loops over the `Product` table instead of Customer.1. Filter: Brand = "Tailspin Toys"
2. Loop through every Product that belongs to Tailspin Toys
3. For each Product, calculate [Sales Amount] for Tailspin Toys
4. Take the AVERAGE of all product totalsExample Calculation:
Let's say Tailspin Toys has 2 products:
Product X: 700.00
Product Y: 407.12
Average = (700 + 407.12) / 2 = 553.56Better name : `Avg Sales per Product by Brand`
Why 'Product' has quotes but Customer doesn't?
Rule in DAX:
- If table name has space or is a reserved word, use single quotes: `'Product Table'`
- If table name is one word and not reserved, quotes are optional: `Customer` = `'Customer'`Both are correct. `'Product'` and `Product` are same. Some people always use quotes to be safe.
Key takeaway:
AVERAGEX loops row by row in the table you give it. Context Transition makes sure that loop respects the Brand filter from your PivotTable row.Hope this clears it! Let me know if you want me to explain with the actual data from the C0247.pbix file.
Hello Shai_Karmani ,
First of all, thank you very much for your reply and the detailed explanation. I'm starting to understand the logic much better.
However, I'm still unable to reproduce the result of 98.58. I added the CustomerKey count to the PivotTable, assuming it represents the number of customers who purchased at least one product.
Instead, I'm getting 90.88. Could you please explain what I'm missing or where my reasoning is incorrect?
Thank you in advance
Best regards
Hello Gautam_Kumar01 ,
Thank you very much! Your answer and Shai_Karmani answer are very similar. I finally understood the concept after reading both of them several times.
For this measure, the explanation that I'll remember is:
Average Sales per Buying Customer = Total Tailspin Toys sales ÷ Number of distinct customers who purchased Tailspin Toys
I also now understand why some table names must be enclosed in single quotes (such as 'Product'), while others, like Customer, do not.
Thanks again to both of you for your clear explanations
Best regards
