When working with DAX in Power BI, there are three critical contexts that you need to understand:

• Row context

• Filter context

• Evaluation context

These contexts determine how DAX expressions are evaluated, and understanding them is crucial to creating accurate and effective measures and calculated columns. In this blog post, we'll explore each context in detail, including how they differ and how to identify which context is in use.

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:

Revenue Percentage = [Revenue] / CALCULATE(SUM([Revenue]), ALL('Sales'[Product]))

In this formula, the context is set to the row level (row context) because we are dividing the revenue of each row by the total revenue of that month, which is the sum of all revenues for all products in that month. The 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:

Total Revenue = CALCULATE(SUM('Sales'[Revenue]), 'Sales'[Product] = "Product A")

In this formula, the context is set to the filter level (filter context) because we are using the 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:

Rank Within Department = RANKX(FILTER('Employees', 'Employees'[Department] = EARLIER('Employees'[Department])), 'Employees'[Salary])

In this formula, the context is set to the row level (row context) because we are ranking employees within each department. The 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.

Average Salary = CALCULATE(AVERAGE('Employees'[Salary]), 'Employees'[Department] = "Sales")

In this formula, the context is set to the filter level (filter context) because we are using the 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.

Total Sales All Years = CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales[Year]))

Here, the ALL function removes the filter context on the Year column and evaluates the expression in the context of all years. As a result, the expression calculates the total sales amount for all years, irrespective of the filter context on the Year column.

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.

Total Sales Product A 2023 = CALCULATE(SUM(Sales[Sales Amount]), FILTER(Sales, Sales[Product] = "Product A" && Sales[Year] = 2023))

Here, the 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.

1. Row context applies to individual rows in a table or column.

2. Filter context applies to the entire data model based on active filters.

3. Evaluation context combines both row and filter contexts to provide a broader context for DAX expressions.

4. 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:

1. Look for the function being used: Some DAX functions can only be used in certain contexts. For example, the CALCULATE function is used in the filter context, while the SUMX function is used in the row context.

2. 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.

3. 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.

It is important to note that in some cases, multiple contexts can be in use simultaneously. In such cases, the formula will evaluate based on the highest priority context.

Conclusion

In summary, the row, filter, and evaluation contexts are essential concepts in DAX. Understanding these contexts and how they interact is crucial to writing effective DAX expressions. By identifying the context in use, you can create the right calculations for your business needs.

I hope you have got a good understanding of the DAX contexts with the examples. If you have liked this post, you may also my other blog posts.

1. Data Modeling in DAX: Everything You Need to Know with Real-World Examples

2. Mastering DAX Basics: Learn the fundamentals of DAX syntax and functions with practical examples

3. Understanding Calculated Columns and Measures in DAX

4. Mastering DAX Context: Understanding Row, Filter, and Evaluation Context