DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.
Context enables you to perform dynamic analysis, in which the results of a formula can change to reflect the current row or cell selection and also any related data. Understanding context and using context effectively are critical for building high-performing, dynamic analyses, and for troubleshooting problems in formulas.
Mainly there are two types of context in DAX,
Row context can be thought of as “the current row.” If you have created a calculated column, the row context consists of the values in each individual row and values in columns that are related to the current row.
Filter context is the set of values allowed in each column, based on filter constraints that were applied to the row or that are defined by filter expressions within the formula.
Let’s look into details through an example with two simple tables,
Sales and Customer tables
To calculate total OZ sales,
OZ_Sales=CALCULATE(SUMX(Sales, Sales[Amount]+0), Customer[Country]=”OZ”)
Step by step detail of above formula,
1. Add “OZ” to the filter context.
2. Get the view of the “Sales” table in current filter context (which now includes “OZ”).
3. Send the new view into the SUMX function, which is to be evaluated.
The view generated above step is used to add new column ‘X’ and the formula used for calculating column ‘X’ is,
4. Sum of the new column X = 250+350+220 = 820.
1. It exists in a calculated column.
For example, we create TotalSales_Column with new column option (Sum of SalesAmount column and SaleTax column) and here we can see that it is a row context,
When we create the same formula by using new measure as shown below, it throws an error with the naked columns which shown below,
When a sum function is added to the naked columns, then the correct result is given.
2. Row context do not follow the relationship, for example when a new calculated column is created under customer table using the sales columns, it throws error which is shown below,
3. By some of the functions like SUMX, the row context can be used in the new measure as shown, Which gives the same result as total sales created with the new measure with sum function and the new column.
- Filter context is provided by the coordinates of the visuals. For example, In the visual, Sales are filtered by name and country columns. Here the filter context is applied by name and country columns.
- A filter context cannot create a row context, it is clear from the result of TotalSale that, the Saleamount is not filtered, that is filter context is not applied.
3. From the point 2 of filter context evaluation, the formulae,
TotalSale created on Customer table was given incorrect results, but if we apply CALCULATE function, then it gives us the correct result as shown below,
- Filter context propagates from one side to many sides of the relationships
- Comes initially from the visual coordinates.
- Can be modified by the functions like CALCULATE().
- Follows the one to many relationships automatically.
- Bidirectional filtering can be forced.
- Exists in calculated columns.
- Exists in special DAX functions like SUMX and FILTER.
- Does not follow relationship.
- A row Context does not create a filter context.