Context in DAX: Row Context and Filter Context

Context in DAX is a fundamental concept that governs how expressions are evaluated in Power BI and other Analysis Services environments.

Understanding context is key to mastering DAX because it controls the behavior of formulas and calculations based on their environment, allowing for dynamic and flexible data analysis.

The two main types of context in DAX are row context and filter context, and each plays a unique role in defining what data is accessible and how calculations are performed.

Row context refers to the current row in a table during a calculation, typical in calculated columns and iterators, where DAX performs evaluations on a row-by-row basis.

Filter context, on the other hand, is the set of filters applied to data, often driven by slicers, report filters, or relationships, that restrict the data scope for aggregations in measures.

The interaction between these contexts can be nuanced, especially when transitioning context through functions like CALCULATE or when nested iterators are used.

This interplay is central to writing effective DAX that adapts dynamically to reporting scenarios and user inputs.

Row Context

Row context exists when a DAX expression is evaluated for each row in a table individually. It acts like a cursor moving through rows, enabling calculations involving the row’s column values.

Usage: Typically present in calculated columns, variables within iterators like SUMX, FILTER, or ADDCOLUMNS.

How it works: When a formula references a column, it retrieves the value from the current row being processed.

Example:

Each row’s profit is calculated individually using row context.

Filter Context

Filter context is the subset of data defined by filters applied in reports, slicers, or by relationships during measure evaluation. It dictates what data is included or excluded during calculation.

Usage: Primarily relevant for measures and aggregations where data is summed, averaged, or counted under certain constraints.

How it works: Filtering data affects what rows are considered for aggregate functions; multiple filters combine logically to restrict the dataset.

Example:

A measure calculating total sales for a specific region considering filters from the report page:

The actual rows summed depend on the current filter context from slicers or report filters.

Context Transition

When functions like CALCULATE() are used within row context, Power BI transforms row-level information into filters applied at the aggregation level.

Importance: Enables complex dynamic calculations such as context-sensitive aggregations or conditional sums.

Example:

Here, the row context of a product transitions to filter context to calculate total sales for that specific product.

Interaction and Practical Implications

1. Row context works well for row-wise calculations but is limited for aggregations without context transition.

2. Filter context controls the interactivity of reports, making calculations responsive to user-selected filters, slicers, and cross-filtering visuals.

3. Misunderstanding context can lead to incorrect results, such as aggregations ignoring filters or row-level calculations summing across entire tables unintentionally.