Power BI DAX Context Explained in Minutes

In Power BI DAX, context is all about the environment that affects how a formula is calculated. It decides which subset of data is used for the calculation and lets you do dynamic analysis based on what users do. There are two main types of context in DAX: Row Context and Filter Context. Knowing this concept helps you create and troubleshoot DAX formulas efficiently.

Row Context

Row context is when a DAX expression is evaluated for each row of a table. It's all about the current row that the formula is working on. For example, When you create a calculated column, it will look at the values from all the columns in that same row. Additionally, If the table is connected to another table, it will pull in related values from that table as well.

To get related values you can use RELATED or RELATEDTABLE Functions in DAX.

Implicit Row Context: This happens naturally in calculated columns where DAX expressions are evaluated row by row. For example, if you create a new calculated column with the DAX expression [SalesAmount] * 1.1, the calculation will be done for each row individually.
Explicit Row Context: This is created using iterator functions like SUMX, AVERAGEX, FILTER, etc., where DAX processes each row of a table in the context of the formula.

Example: Figuring Out Total Sales for Each Order

Let's say you've got a table named Sales with columns like OrderID, ProductID, Quantity, and UnitPrice. You want to work out the total sales value for each order.

Total Sales = SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

In this example:

SUMX is a function that goes through each row in the Sales table one by one.
The formula Sales[Quantity] * Sales[UnitPrice] is calculated for each row individually.

Filter Context

Filter context is all about how data gets filtered before any calculations happen. It's the set of filters that decide what data is used in the calculation. You can change and create filter context with different DAX functions, slicers, and report filters.

Implicit Filter Context: This happens automatically with visualizations, slicers, and filters in a Power BI report. For example, if you pick a specific product category in a slicer, the filter context will only include rows for that category.
Explicit Filter Context: You create this using DAX functions like CALCULATE,CALCULATETABLE,FILTER, And ALL, which change the filter context in the expression.

Example: Figuring Out Total Sales for a Specific Region

Let's say you've got a Sales table with columns SalesAmount and Region. You want to find out the total sales amount just for the "North America" region.

Total Sales North America = CALCULATE(
    SUM(Sales[SalesAmount]),
    Sales[Region] = "North America"
)

In this example:

CALCULATE changes the filter context to only include rows where Sales[Region] is "North America".
SUM(Sales[SalesAmount]) is then calculated within this new filter context.

Interactions Between Row and Filter Context

In many cases, both row context and filter context work together. For example, when you use an iterator function inside CALCULATE, the row context from the iterator interacts with the filter context set by CALCULATE.

Example: Average Sales for Orders in a Specific Region

Imagine you want to find out the average sales amount for each order in the "North America" continent. You've got a Sales table with Quantity, UnitPrice and Region columns.

Average Sales North America = CALCULATE(
    AVERAGEX(
        Sales, 
        Sales[Quantity] * Sales[UnitPrice]
    ),
    Sales[Region] = "North America"
)

In this combined example:

CALCULATE at the outer level changes the filter to only include rows where Sales[Region] is "North America".
AVERAGEX goes through each row in Sales table, creating a specific row context for each one.
The expression Sales[Quantity] * Sales[Unit Price] works out the sales amount for each row.

So, we mix explicit row context (using AVERAGEX) and explicit filter context (using CALCULATE).

Summary

To wrap it up, DAX context is key to how calculations run in Power BI. There are two main types: Row Context and Filter Context. Knowing how to use these contexts well lets you do powerful and dynamic data analysis in your Power BI reports and models.