Excel DAX - Evaluation Context
In DAX, context is an important term that you should be aware of, while writing DAX formulas. Also referred to as evaluation context, DAX context is used to determine the evaluation of a DAX formula and the corresponding result. This means, the results of a DAX formula can vary according to the context. You should clearly understand how a specific DAX context is used and how the results can be different.
Evaluation context enables you to perform dynamic analysis, in which the results of a DAX formula can change to reflect the current row or a cell selection and also any related data. Understanding context and using context effectively are very important to build powerful DAX formulas, perform dynamic data analysis, and troubleshoot problems in DAX formulas. Evaluation contexts are the basis of all of the advanced features of DAX that you need to master to create complex data analysis reports.
As you keep referencing to DAX functions for relevant usage in DAX formulas, you need to refer to this chapter on DAX context to obtain clarity on the results.
Types of Context in DAX
DAX supports the following evaluation contexts −
- Row Context
- Filter Context
When a DAX formula is evaluated, all the contexts will be taken into account and are applied as relevant. The contexts exist together and the result of the formula will be different based on the context that is used while calculating a value. For example, when you select fields for rows, columns, and filters in a PivotTable, the subtotals are dynamically calculated based on which row and which column the subtotal/total is associated with and the values in the rows and columns are determined by the filters used.
Row Context
Row context means that the DAX formula or the DAX function knows which row of the table it is referencing at any point in time. You can consider row context as the current row. The formula will get calculated row-by-row with the row context.
Some DAX functions (e.g., the X-functions, FILTER ()) and all calculated columns have a row context. For example, if you create a calculated column Year with the DAX formula = YEAR ([Date]), the values of the calculated column are obtained by applying the given DAX formula on the given column in the table, row by row.
This means that if you have created a calculated column, the row context consists of the values in each individual row and the values in the columns that are related to the current row, as determined by the DAX formula used. Though the DAX formula does not contain the reference to a row, DAX implicitly understands the row context while calculating values.
DAX creates a row context automatically when you define a calculated column and all the calculated values with the DAX formula used will appear in the calculated column.
In contrast, when you have a DAX function such as SUMX, the values calculated row by row get summed up and only the final result will be displayed. That is, the intermediate values are discarded.
When you have related tables, the row context determines which rows in the related table are associated with the current row. However, the row context does not propagate through relationships automatically. You have to use the DAX functions - RELATED and RELATEDTABLE for this.
Multiple Row Context
DAX has iterator functions like SUMX. You can use these functions to nest row contexts. With this, programmatically you can have a recursion over an inner loop and an outer loop, where you can have multiple current rows and current row contexts.
For example, you can use the DAX function Earlier () that stores the row context from the operation that preceded the current operation. This function stores two sets of context in memory - one set of context represents the current row for the inner loop of the formula, and another set of context represents the current row for the outer loop of the formula. DAX automatically feeds the values between the two loops so that you can create complex aggregates.
For an example, refer to the scenario - Creating a DAX Formula that Dynamically Ranks Values in the chapter Scenarios - Ranking and Comparing Values.
Filter Context
Filter context refers to any filtering that is applied to the Data Model in DAX. Filter context is created by a PivotTable and also by the DAX functions.
Filter Context Created by a PivotTable
Filter Context created by a PivotTable is the natural filtering that is applied by the selections made on the PivotTable fields from the following −
- Rows
- Columns
- Filters
- Slicers
The filter context created by a PivotTable, filters the underlying tables in the Data Model. If the tables are related, then the filters flow down from the lookup tables to data tables. That means, you can filter the data tables based on the results from the lookup tables. The filter propagation does not happen the other way round. However, you can use DAX formulas to filter the lookup tables based on the results from the data tables.
Filter Context Created by DAX Functions
You can use DAX Filter functions to define calculated fields and calculated columns, containing filter expressions that control the values used by the DAX formula. These calculated fields and calculated columns then become part of the PivotTable fields list and you can add them to the PivotTable. You can also selectively clear the filters on particular columns with these DAX Filter functions. An example of a powerful DAX Filter function to create Filter Context is CALCULATE (). For an example, refer to the chapter Scenarios - Performing Complex Calculations.
Filter Context as an Addition to Row Context
Row context does not automatically create a filter context. You can achieve the same with the DAX formulas containing DAX Filter functions.
