What is Row Context and Filter Context in Power BI DAX Tutorial

In Data Analysis Expressions (DAX), used primarily in Microsoft Power BI, Power Pivot, and Analysis Services, understanding row context and filter context is fundamental to creating effective and efficient measures and calculations. Here’s a detailed explanation of each:

Row Context

Row context refers to the context within a row of a table. When you use row context, DAX evaluates an expression for each row individually. This context is automatically created when you:

• Use a calculated column: Each row is evaluated independently.
• Use iterating functions like SUMX, FILTER, AVERAGEX, etc., which process row by row.

Example

Consider a table Sales with columns Quantity and UnitPrice. To create a calculated column for TotalSales (quantity multiplied by unit price), you would write:

TotalSales = Sales[Quantity] * Sales[UnitPrice]

Here, the row context ensures that for each row, the Quantity and UnitPrice from that specific row are used in the calculation.

Filter Context

Filter context is a more complex concept and refers to the set of filters applied to data when a DAX expression is evaluated. Filter context is created by:

• Explicit filters applied in the report (like slicers, page filters, etc.).
• Calculated columns and measures that inherently apply filters.
• DAX functions like CALCULATE, which can modify the filter context.

Filter context combines all the filters that are applied through various means (including user selections in a report) to determine what data is visible and therefore what data is used in a calculation.

Example

Suppose you have a measure that calculates the total sales:

TotalSales = SUM(Sales[TotalSales])

When you add this measure to a report with a slicer for Year, the filter context changes based on the year selected. For example, if 2014 is selected in the slicer, the filter context ensures that only sales from the year 2019 are considered in the TotalSales measure calculation.

CALCULATE and Filter Context

The CALCULATE function is powerful because it allows you to modify filter context within a measure. For example:

Sales2020 = CALCULATE(SUM(Sales[TotalSales]), Sales[Year] = 2020)

Here, CALCULATE changes the filter context to include only rows where Year is 2020.

Interaction Between Row Context and Filter Context

When row context and filter context intersect, things can get more complex. For example, in an iterating function (which creates row context), you might need to reference the current filter context:

Example with SUMX and CALCULATE

TotalSalesAboveThreshold = 
    SUMX(
        FILTER(
            Sales, 
            Sales[TotalSales] > 100
        ), 
        Sales[TotalSales]
    )

Here, FILTER iterates through each row in the Sales table (creating a row context) and uses the filter context to determine if TotalSales is greater than 100. The SUMX then sums the TotalSales values of the filtered rows.

Summary

• Row context: Operates on individual rows, commonly used in calculated columns and iterating functions.
• Filter context: Applies filters to determine what data is visible, crucial for measures and influenced by report elements and DAX functions like CALCULATE.

Understanding these contexts and their interactions is crucial for writing efficient and accurate DAX code in Power BI and other related tools.