Here’s a detailed and comprehensive explanation of Row Context and Filter Context in DAX (Data Analysis Expressions) within Power BI.

Understanding Row Context and Filter Context in DAX

DAX (Data Analysis Expressions) is the formula language used in Power BI, Power Pivot, and SSAS Tabular models. Two of the most fundamental and often misunderstood concepts in DAX are Row Context and Filter Context. These contexts define how calculations work within a DAX formula, influencing how values are evaluated within a dataset.

1. What is Context in DAX?

Context in DAX determines how a formula is evaluated based on the surrounding conditions, such as the filters applied to a dataset or the current row being processed. There are two primary types of context in DAX:

Row Context – Related to the current row in a table.
Filter Context – Related to filters applied from reports, visuals, or DAX formulas.

Understanding these contexts is critical for writing correct and optimized DAX formulas.

2. Understanding Row Context in DAX

Definition of Row Context

Row Context is the context in which a DAX formula operates on a single row of a table at a time. This means that when you write a calculated column formula, the formula is evaluated row by row.

Where Does Row Context Apply?

Row Context exists in:

Calculated Columns
Row-by-row Iterating Functions like SUMX, AVERAGEX, etc.

Example of Row Context

Consider the following table named Sales:

OrderID	Product	Quantity	Unit Price	Total Sales
1	Laptop	2	800	?
2	Phone	3	500	?
3	Tablet	5	300	?

We need to calculate the Total Sales as: Total Sales=Quantity×Unit Price\text{Total Sales} = \text{Quantity} \times \text{Unit Price}

Using a Calculated Column, we write the DAX formula:

Total Sales = Sales[Quantity] * Sales[Unit Price]

The formula runs row by row and computes values individually for each row in the table.
This is Row Context because DAX is aware of the current row and evaluates the expression accordingly.

Row Context in Iterating Functions

Row Context is also present in iterating functions like SUMX(), AVERAGEX(), MAXX(), etc. These functions evaluate expressions row by row.

For example, using SUMX():

Total Sales Sum = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

SUMX iterates through each row of the Sales table, applies the multiplication, and sums up the results.
This is different from a simple SUM(Sales[Total Sales]), which just adds up values in a column.

3. Understanding Filter Context in DAX

Definition of Filter Context

Filter Context refers to the set of filters applied to a calculation before the formula executes. These filters can come from:

Slicers
Filters on visuals
Row and column filters in Pivot Tables
DAX Functions like FILTER() or CALCULATE()

Where Does Filter Context Apply?

Filter Context applies in:

Measures
DAX expressions that modify filters (e.g., CALCULATE, FILTER)
Visual filters (slicers, report filters, etc.)

Example of Filter Context

Consider a Sales table with a Region column:

Region	Product	Sales Amount
East	Laptop	10,000
West	Phone	7,500
East	Tablet	5,000
West	Laptop	8,500

If we create a Measure to sum Sales Amount:

Total Sales Measure = SUM(Sales[Sales Amount])

In a Power BI report, if we filter by Region = “East”, the measure automatically updates to only sum sales in the East region.
This is Filter Context because the measure calculation depends on the current filters applied.

4. Combining Row Context and Filter Context

Often, both contexts are needed in a calculation.

Example: Using CALCULATE to Change Filter Context

Suppose we want to calculate Total Sales for only Laptops irrespective of the filters applied in visuals.

Total Sales for Laptop = CALCULATE(SUM(Sales[Sales Amount]), Sales[Product] = "Laptop")

CALCULATE() modifies the filter context so that the measure always considers only Laptop sales, even if a slicer filters by a different product.

Example: Row Context Alone Does Not Apply Filters

If we use SUMX() without modifying filter context:

Total Sales SumX = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

This will calculate row by row but will not override any existing filters.
If the visual is filtered for “East Region”, only sales from East will be considered.

5. How to Handle Context Conflicts

Sometimes, we need to bridge Row Context and Filter Context.

Using EARLIER() to Access Previous Row Context

Consider we need a Running Total column:

Running Total = 
VAR CurrentRow = Sales[Sales Amount]
RETURN SUMX(FILTER(Sales, Sales[Sales Amount] <= CurrentRow), Sales[Sales Amount])

This ensures the total is cumulative for each row, handling Row Context inside a filter context.

Using CALCULATE to Switch Contexts

Another example:

Total Sales All Products = CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales[Product]))

The ALL() function removes filters from Product, forcing the calculation to include all products.

6. Key Takeaways

Row Context operates row by row, mainly in calculated columns and iterating functions.
Filter Context applies filters to tables, primarily in measures and slicers.
SUMX(), AVERAGEX(), etc., apply Row Context in measures.
CALCULATE() modifies Filter Context, allowing overrides.
EARLIER(), ALL(), and FILTER() help manage Context Transition.

Conclusion

Understanding Row Context and Filter Context is crucial for writing efficient DAX formulas in Power BI. Knowing when and how to use iterators (SUMX), filter modifiers (CALCULATE, FILTER, ALL), and context transition functions (EARLIER, ALLEXCEPT) will improve your analytical capabilities. Mastering these concepts will allow you to create more powerful and flexible Power BI reports.

Let me know if you need further clarifications!