The Complete DAX Guide in 2026: CALCULATE, Filter Context, and Performance Without the Mystery

If you've ever opened Power BI, written a SUM, watched it work, written a CALCULATE, watched it work too... and then written something slightly more complex and got the wrong number, welcome to the club. DAX is like that: easy until it isn't.

The good news? About 90% of the DAX mistakes you make trace back to three or four core ideas. Once those click, you stop "memorizing formulas" and start designing them. As a bonus, in April 2026 Microsoft shipped a preview of DAX User-Defined Functions (UDFs), which slightly changes how we reuse logic in a model. We will cover those at the end.

The goal here is simple: explain CALCULATE, contexts, ALL/ALLSELECTED, time intelligence, and performance the way you would explain them over coffee, not the way a manual would.

Why DAX is the most expensive and most misunderstood Power BI skill

DAX is simultaneously the most sought-after skill for analysts and the hardest one to explain clearly. Microsoft itself says that understanding and using context effectively is critical for high-performance formulas, dynamic analysis, and troubleshooting. Translation: if you do not understand context, you are guessing.

The trap is not syntax. The trap is that DAX looks like SQL, looks like Excel, but is neither. It has its own rules. If you bring spreadsheet thinking into a tabular model, things fall apart quickly.

Let's get to the point.

Row context and filter context: the two parallel worlds of DAX

Every DAX formula lives inside one or two contexts at the same time.

Row context is the "world of the row". You only get row context when DAX iterates line by line - in calculated columns and inside iterators such as SUMX, AVERAGEX, and FILTER. Inside row context you can reference columns directly: Sales[Quantity] * Sales[UnitPrice] works because DAX knows which row it is evaluating.

Filter context is the "world of the slice". It comes from visuals, slicers, and CALCULATE arguments. It is the full set of active filters at that moment. When you place Category on the rows of a matrix and Total Sales on the values, each cell gets a different filter context.

The classic mistake is writing Sales[Quantity] * Sales[UnitPrice] inside a measure and expecting it to work. Measures do not have row context. Measures live in filter context. There is no "current row" inside a measure.

The fix is SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]). SUMX creates the row context that was missing.

That leads to the first golden rule: measure = filter context, calculated column = row context. Whenever you need one in the role of the other, you bring in an iterator or CALCULATE.

CALCULATE: the function that literally changes the game

CALCULATE is the only DAX function that changes filter context. Think of it as a teleporter: you pass an expression and a set of filters, and it evaluates that expression in a parallel universe where those filters are active.

Electronics Sales =
CALCULATE(
    [Total Sales],
    Products[Category] = "Electronics"
)

That Products[Category] = "Electronics" is syntactic sugar. Under the hood it becomes FILTER(ALL(Products[Category]), Products[Category] = "Electronics"). That matters because ALL wipes any existing filter on Category before applying the new one. That is why CALCULATE overrides filters by default.

The other key concept is context transition. When you call CALCULATE inside a row context, DAX automatically converts the current row into filter context.

Imagine a calculated column on the Products table.

Product Sales = CALCULATE([Total Sales])

No filter. No extra logic. Yet it works because CALCULATE transforms the current row ("I am product SKU-123") into a filter ("filter context: Product = SKU-123"), and then the measure evaluates for that product only.

That single idea, context transition, explains a huge share of "why is this number wrong?" moments. Whenever you see a measure called inside SUMX, AVERAGEX, FILTER, or a calculated column, stop and think. Transition is happening.

ALL vs ALLSELECTED: the duo nobody gets on the first try

Let's settle this.

`ALL` removes all filters from a table or column. It ignores visuals, slicers, everything. Total reset.

`ALLSELECTED` removes filters internal to the current visual, but keeps what the user selected in slicers and outer filters. Think of it as "ignore only the matrix breakdown, keep the broader user selection".

A practical example is "% of total" in a matrix with Category and Subcategory.

% of Grand Total =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Products)))

This always divides by the absolute total, regardless of what the user selected. Good for "absolute share".

% of Selected Total =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Products)))

This one respects the slicer. If the user selected Q1, the "total" becomes the Q1 total.

Rule of thumb: use ALL when you want to ignore the user. Use ALLSELECTED when you want to respect the user but ignore the visual's own grain.

Time intelligence: SAMEPERIODLASTYEAR, DATEADD, and the date table

Time intelligence has one non-negotiable requirement: a proper date table, marked as a date table, continuous, and complete for the entire period. Without it, things do not behave consistently.

Once the date table is in place, year-over-year becomes a one-liner.

Sales Previous Year =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))

SAMEPERIODLASTYEAR takes the date interval currently in filter context and returns the same interval one year earlier. If the user is looking at "May 2026", it returns "May 2025".

DATEADD is the more flexible sibling.

Sales 3 Months Ago =
CALCULATE([Total Sales], DATEADD(DimDate[Date], -3, MONTH))

Sometimes it helps to add ALL(DimDate) to clear filters applied by the visual itself. Not always - only when you need to compare the same period while ignoring the current date slice.

YoY is the classic combination.

YoY % =
VAR Current = [Total Sales]
VAR Previous = [Sales Previous Year]
RETURN DIVIDE(Current - Previous, Previous)

Notice the VAR. That takes us straight to the next topic.

Performance: VAR is your best friend, nested iterators are not

When a measure takes too long to open, the usual causes are repeated calculations, nested iterators, or too much work pushed into the Formula Engine.

Use VAR whenever you can

-- Bad
Margin % =
DIVIDE(
    [Total Sales] - [Total Cost],
    [Total Sales]
)

Looks harmless, but [Total Sales] gets evaluated twice.

-- Better
Margin % =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
RETURN DIVIDE(Sales - Cost, Sales)

Now each value is calculated once. The formula becomes faster, clearer, and less fragile when combined with CALCULATE or context transition.

Nested iterators: the silent killer

Each level of iteration multiplies the work. A SUMX inside another SUMX can blow up the number of calculations and choke the Formula Engine.

Problematic pattern.

Total Effort =
SUMX(
    Products,
    SUMX(
        RELATEDTABLE(Sales),
        Sales[Quantity] * Products[BasePrice]
    )
)

Most of the time you can rewrite that as a single SUMX over the fact table and let the storage engine, the famous "VertiPaq", do the heavy lifting.

Total Effort =
SUMX(
    Sales,
    Sales[Quantity] * RELATED(Products[BasePrice])
)

Rule of thumb: iterate the fact table once and pull dimension columns with RELATED.

Measure or calculated column?

Calculated columns are processed at refresh time, stored in memory, and take up model space. Measures are evaluated at query time, based on what the user requested.

Rule: if the value depends on user selection, it should be a measure. If it is an intrinsic row attribute, such as product category or customer age bracket, it can be a column. Do not use calculated columns to "cache" totals.

The 2026 novelty: DAX User-Defined Functions

In April 2026, Microsoft shipped DAX User-Defined Functions in preview for Power BI Desktop. The idea is to package reusable DAX logic inside the model itself.

Instead of copy-pasting the same expression across many measures, you define a function once and use it everywhere.

FUNCTION Margin(sales, cost) =
    DIVIDE(sales - cost, sales)

Then, in any measure.

Product Margin = Margin([Total Sales], [Total Cost])

The benefit is cleaner reuse. The caution is that the feature is still in preview, so performance and debuggability should be tested carefully.

A practical starting point is to use UDFs for pure utilities such as formatting, math helpers, and string transforms, while keeping complex business measures traditional for now.

What to remember tomorrow morning

• Filter context lives in measures, row context lives in iterators and calculated columns. Use CALCULATE or an iterator when you need to switch between them.
• CALCULATE overrides filters and triggers context transition when called inside row context.
• ALL is a total reset, while ALLSELECTED respects the broader user selection.
• Time intelligence requires a proper date table. SAMEPERIODLASTYEAR and DATEADD cover most comparisons.
• For performance, use VAR, avoid nested iterators, and iterate the fact table once when possible.
• A measure is not the same thing as a calculated column. Do not use columns to cache totals.
• DAX UDFs, released in preview in April 2026, are the new path for reusable logic.

DAX rewards people who understand the rules and punishes people who memorize formulas. Whenever you feel stuck, come back to the most important question: "what context am I in at this point of the formula?"