I’ve practiced context transition a lot recently. I’ll review what I’ve learned and revisit the theory from book ‘The Definitive Guide to DAX’.
Learning Loop
Over the years I’ve developed a ‘learning loop‘. Something like this:
- start with basic theory
- practice, create, discover
- revisit theory for deeper understanding
- practice, create, discover
Source
All quotes in this post are from the amazing book ‘The Definitive Guide to DAX’ 2nd edition (link) by Marco Russo & Alberto Ferrari.
Basic Theory
Evaluation Context
It consists of filter context and row context.
The filter context filters data, whereas the row context iterates tables. When DAX is iterating, it is not filtering; and when it is filtering, it is not iterating.
Chapter 4 Understanding Evaluation Context
I understood the basic theory/concepts well:
- filter context: all fields in viz row/column and in slicers/filters
- row context: used by X functions (SUMX) and calculated columns
CALCULATE Function
Here’s the common way that CALCUALTE is used:
The real power of CALCULATE is that it offers many options to manipulate a filter context.
Chapter 5 Understanding CALCULATE and CALCULATETABLE
I studied many examples of how CALCULATE manipulates filter context.
Context Transition
This wasn’t easy to understand. But I had to start somewhere.
There is one operation performed by CALCULATE that can transform a row context into a filter context. It is the operation of context transition… It automatically adds as filter arguments all the columns that are currently being iterated in any row context–filtering their actual values in the row being iterated.
Chapter 5 Understanding context transition
After taking two courses and tons of reading I still didn’t fully understand this. I decided to practice and hopefully unlock this concept.
Practice Create Discover I
A specific example is essential. I’ll review post Top Product Sales by State:
Requirement
Per State display largest individual Product Code aggregated sales amount.
Final Visual
I only want to show State and the measure like this:
Final Measure
Top Product Sales =
MAXX(
'Product',
CALCULATE(
SUMX(Sales,
Sales[Quantity] * Sales[Price]
)
)
)
Per ‘State’ MAXX iterates each Product table row, does a SUMX in Sales table, keeps largest aggregated amount.
Why is CALCULATE needed?
This took soooo long to understand. Back to the basics:
Q: What’s in the viz filter context? A: ‘Product'[State] that’s it! ‘Product'[Code] is not in the viz filter context.
Q: X functions do what? A: They create row context to iterate a table.
Remember: filter context filters (doesn’t iterate), row context iterates (doesn’t filter). They can both exist but do different things.
Think in Steps:
- Viz filter context = ‘Product'[State] only
- CALCULATE adds all ‘Product’ table fields to filter context
- SUMX: [Quantity] * [Price] for each [State] & [Product Code] combo
- Keep max aggregated sales amount for each [State]
Observation
Why not include [Product Code] in the viz so it’s in initial filter context? The viz would be bigger and require a lot of scanning to find the largest amount.
!Study The Errors!
I didn’t directly write the final DAX measure. It was a long journey of measures that didn’t work but along the way I had several eureka moments!
I recommend reviewing the 3 incorrect solutions (link). I understand now!
Revisit Theory
Chapter 5 quotes
I re-read chapter 5 several times. I underlined, added notes, etc. Examples:
The filter context reduces the number of rows iterated by SUMX while scanning Sales.
Chapter 5 page 149
Simple but so important to understand and visualize in my mind.
The filter context works on the entire model, filtering both Product and Sales tables
Chapter 5 page 149
Vital to understand relationships between tables and how they filter.
During the iteration of Sales, SUMX only scans the visible portion of the Sales table, generating a row context for each visible row.
Chapter 5 page 149
I get it! Filter context filters and then SUMX iterates remaining rows.
A row context exists whenever an iteration is happening on a table. Inside an iteration are expressions that depend on the row context itself
Chapter 5 page 151
My expression that depends on row context: Sales[Quantity] * Sales[Price].
Not Yet…
I don’t understand pages 151 to 153. I’ll come back to that later.
Key Concept
Can I add ‘Product Code’ to filter context via a measure? Yes. Include CALCULATE to force context transition (all product table fields added to filter context).
Final filter context = initial from viz + any context transition via DAX.
There were also many other key concepts but time to practice again!
Practice Create Discover II
Alternative DAX Solution
Q: Why does this measure also give the correct answer? A: RELATEDTABLE can also force context transition!
Top Product Sales2 =
MAXX(
'Product',
SUMX(RELATEDTABLE(Sales),
Sales[Quantity] * Sales[Price]
)
)
DAX Guide
This is when I realized something interesting in DAX Guide. I can filter it to show only functions that perform context transition.
I highly encourage you to play with the filters and options!
Another Post
I also explored context transition in post Learning DAX by experimentation via trial and error. I just re-read my post. Wow! Great practice.
Summary
I vastly increased my knowledge: (theory + practice) X determination.
The Journey
Why only share final theory/output? It’s more important to share my journey so others won’t get discouraged when they struggle along the way.
As in the book ‘Burn Math Class’, I can’t just memorize theory. I have to discover it myself and invent my own terms to describe it.
What’s Next?
I’ll go back and re-examine complex DAX measures that I probably didn’t fully understand or did and forgot. Then I’ll create more puzzles to solve.
I still have SO much to learn but it’s a great challenge!