Mastering DAX Context: Understanding Row, Filter, and Evaluation Context
- Row context
- Filter context
- Evaluation context
Row Context
Row context is the context in which a DAX expression is evaluated for each row in a table or column. It is the default context in DAX. When a DAX formula references a column in a table, it is evaluated row by row, with the result of the expression for each row being returned as a table. The row context is determined by the current row of the table that is being evaluated. The row context is also used to resolve column references in expressions, so when you reference a column in an expression, the value of that column is used in the context of the current row. For example, suppose you have a sales table with columns for sales amount and salesperson name. If you create a calculated column that multiplies the sales amount by 2, the row context will be the current row of the sales table. For each row, the calculated column will multiply the sales amount by 2 and return the result.Filter Context
Filter context refers to the set of filters that are currently applied to the data model. These filters can come from slicers, visualizations, or other sources. When a measure or calculated column is evaluated in filter context, it considers only the data that matches the active filters. Filter context is essential for creating accurate and relevant measures and calculated columns. By using filters, you can ensure that your calculations are based only on the data that is relevant to your analysis. For example, suppose you have a sales table with columns for sales amount and salesperson name. If you apply a filter to show only sales made by “Raj,” then any measure or calculated column that references the sales amount column will only consider the sales made by Raj. This is because the filter context is set to include only those rows where the salesperson name is “Raj.”Row and Filter context examples
Let’s look at a few more examples for row and filter context to understand it better. Let’s say we have a table named “Sales” with columns “Product”, “Month”, and “Revenue”. We want to calculate the revenue percentage for each product relative to the total revenue for that month. We can use the below DAX formula:CALCULATE function modifies the filter context to include all products for that month by using the ALL function.
If we want to calculate the total revenue for a specific product over all months. We can use the following DAX formula:
CALCULATE function to filter the rows where the product is “Product A”. The function creates a filter context where only the rows with “Product A” are included, and then calculates the sum of revenue for those rows.
Suppose we have a table named “Employees” with columns “Employee Name”, “Department”, and “Salary”. We want to add a calculated column that will rank employees by salary within each department. We can use the following DAX formula:
FILTER function creates a filter context where only the rows with the same department as the current row are included, and the EARLIER function is used to reference the department value of the current row. The RANKX function then computes the rank of the current row’s salary within the filtered rows.
If we want to calculate the average salary for employees in a specific department.
CALCULATE function to filter the rows where the department is “Sales”. The function creates a filter context where only the rows with “Sales” are included, and then calculates the average salary for those rows.
Evaluation Context
The evaluation context refers to the context in which a DAX expression is evaluated. It is determined by the combination of row context and filter context. This context is the most complex of the three contexts, as it involves combining the two other contexts. Unlike row and filter contexts, which are implicit, the evaluation context can be explicitly defined using DAX functions like CALCULATE, CALCULATETABLE, and FILTER. The evaluation context can be thought of as a temporary table that is created based on the filters and relationships that are active at a given point in time. This temporary table is used to calculate the results of a formula. The context can be modified using DAX functions to add or remove filters or change the relationships that are active. For example, suppose you have a sales table with columns for sales amount and salesperson name. If you create a measure that calculates the total sales amount for each salesperson, the evaluation context will be a combination of row context and filter context. The row context will be the current salesperson, and the filter context will be any active filters applied to the data model. To understand evaluation context better, let’s look at a few more examples.Evaluation Context Example: Using the ALL Function to Override Filters
Suppose you have a sales table with columns such as Sales Amount, Product, and Year. You want to calculate the total sales amount for all years, irrespective of the filter context on the Year column. In this case, you can use the ALL function to remove the filter on the Year column and evaluate the expression in the context of all years.Evaluation Context Example: Using the FILTER Function to Create a New Filter Context
Suppose you have a sales table with columns such as Sales Amount, Product, and Year. You want to calculate the total sales amount for a specific product, such as “Product A,” in a specific year, such as “2023.” In this case, you can use the FILTER function to create a new filter context and evaluate the expression in the context of the filtered rows.FILTER function creates a new filter context by filtering the rows where the Product column is “Product A” and the Year column is 2023. The expression then evaluates in the context of the filtered rows and calculates the total sales amount for the specified product and year.
Differences between Row Context, Filter Context, and Evaluation Context
The primary difference between row context, filter context, and evaluation context is the scope of the context.- Row context applies to individual rows in a table or column.
- Filter context applies to the entire data model based on active filters.
- Evaluation context combines both row and filter contexts to provide a broader context for DAX expressions.
- Another difference between these contexts is how they are created. Row context is created by the current row of a table or column, while filter context is created by active filters in the data model. Evaluation context is created by the combination of row and filter contexts.
Identifying Which Context is in Use
Identifying which context is in use is critical to creating accurate and effective measures and calculated columns. One way to identify the context is by looking at the DAX expression being used. If the expression references a column in a table, it is likely using row context. If it references a measure, it is likely using filter context. If it combines both, it is likely using evaluation context. Sometimes, identifying the context in use can be confusing. Here are some tips to help you identify the context in use:-
Look for the function being used: Some DAX functions can only be used in certain contexts. For example, the
CALCULATEfunction is used in the filter context, while theSUMXfunction is used in the row context. - Examine the formula syntax: The syntax of a DAX formula can give you a clue as to the context in use. For example, if a formula contains a filter expression enclosed in square brackets, it is likely to be operating in the filter context.
- Check the column and table names: The column and table names used in a DAX formula can also indicate the context in use. For example, if a formula references a column that is not in the current table, it is likely to be using the row context.