Forum Discussion
Context Transition in DAX
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
Hi 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 totals
Example 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.58
Better 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 totals
Example Calculation:
Let's say Tailspin Toys has 2 products:
Product X: 700.00
Product Y: 407.12
Average = (700 + 407.12) / 2 = 553.56
Better 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.