Understanding context within DAX is something I initially found confusing , as I was unsure of how to manipulate it and couldn't comprehend its relevance. In reality, it is much more simple than I had realised:
What is Context in DAX?
Context in DAX formulas is not a filter, feature, or setting that you turn on or off - it is simply a concept that describes how DAX processes the formulas you write in.
Moreover, it is simply something that needs bearing in mind when writing DAX so your outputs are accurate and behave as intended.
It helps DAX to decide:
- What data to look at
- What values are in/out of scope
- How to evaluate your expression/formula
There are 3 Types of Contexts in DAX
1 - Row Context
This describes certain DAX formulas that evaluates tables one row at a time.
Example - calculated columns
- All calculated columns utilise row context because they produce values at the row level.
- DAX uses row context in this situation to ensure that it only uses values in the same row to produce the output
- For example
Here, Profit is a calculated field that does [Sales] - [Cost].
For item B, the Profit is 5 because 20-15 = 5 .
Row context ensures that DAX evaluates the expression using only the values from the current row; it is essentially a guide for the formulae to work at the row level. Without row context, DAX wouldn't know which row’s Sales and Cost values to use, so the formula wouldn’t work correctly (for Profit for item B, it wouldn't know if to take sales from item A or C, or which cost to use). If it is uncertain of which value to use, it will likely return an error with no output.
While this concept may seem automatic to us, DAX needs this structure to evaluate each row independently.
2 - Filter Context
This is where DAX determines which rows of data to include in a calculation, based on the filters applied to the report or visual.
Where filter context is applied in DAX:
- When an attribute/dimension field (such as Category) is added to a visual
- Using a slicer to select specific values
- Applying filters via the Filter Pane
- Using the CALCULATE() function
- CALCULATE() allows you to evaluate an expression given one or more filters (or "filter contexts").
- The syntax for this is CALCULATE(<The measure or calculation you wish to evaluate>,<filter1>,<filter2>,…)
- These filters can override or extend filters coming from visuals (like slicers or fields)
3 - Query Context
Query context defines how the data should be grouped for a visualisation. When you create a table or chart (such as such as Sales per Category), it tells the DAX to group rows based on Category so that the rows are aggregated in the view.
In doing so, it is essentially telling the FILTER CONTEXT (see above) how to filter the rows based on this grouping.
So when you create a table or chart showing Sales per Category, query context is telling DAX "show me the total sales for each unique Category".
By grouping by category, filter contexts are then naturally created for each value, so:
- For Category A, the filter context will be Category = A, so rows with different categories are removed.
- For Category B, the filter context will be Category = B, so rows with different categories are removed.
So, query context leads to the generation of filter context in visuals — but filter context is what DAX actually uses to calculate results.