# DAX, Deep Dive

> Why does the same DAX measure show a different number on a card, in a table, and next to a slicer? This guide teaches the real reasoning underneath DAX - row context, filter context, CALCULATE, and the patterns and performance habits that come from actually understanding it.


---

# DAX, Deep Dive

You've got a working model. You've written a few measures - a `SUM`, maybe a ratio, maybe your first
`CALCULATE`. Then it happens: the same measure shows one number on a card visual, a different number
in a table broken out by month, and a number you can't explain at all next to a slicer. Nothing crashed.
There's no red squiggly line. The formula is just... telling you something different depending on where
you put it.

That's not a bug in Power BI, and it's not you being bad at this. It's DAX doing exactly what it was
designed to do, and the design is the part nobody explained to you. Every DAX formula is evaluated
inside an invisible, shifting frame called **context** - and until you can see that frame, DAX looks
like magic that occasionally turns on you. Once you can see it, the "weird" behavior turns out to be
the single most useful feature in the language.

This guide is the missing explanation. Not a syntax reference, not a list of functions to memorize -
the actual mental model: what row context and filter context *are*, why `CALCULATE` is the function
that bends them, what "context transition" really does when a measure meets a row, and how to write DAX
you can read six months from now and still trust. We finish with the part almost nobody teaches until
something is already slow: how the VertiPaq engine actually stores your data, and why that explains
which formulas are fast and which ones quietly ruin your refresh time.

This is the deep half. It assumes you can already build a model and write a basic measure - if `SUM`,
relationships, and your first `CALCULATE` still feel shaky, spend time with
[Power BI From Zero](/guides/power-bi-from-zero) phases 4 through 7 first, then come back. Everything
here builds directly on that foundation.

## How to read this

- **Read phases 1 and 2 in order, slowly.** Row context vs. filter context (phase 1) and CALCULATE's
  context transition (phase 2) are the two ideas everything else in DAX is built from. Rush these and
  every later phase will feel like memorizing tricks instead of understanding a system.
- **Already comfortable with context and CALCULATE?** Jump straight to
  [Phase 3: Common DAX Patterns](03-common-dax-patterns-running-totals-yoy-ranking-t.md) for the running-totals, year-over-year,
  ranking, and top-N formulas you'll actually reuse on the job.
- **Writing DAX that works but that you can't explain to a teammate?** Phase 4 is for you - variables,
  debugging technique, and what makes a measure readable instead of a wall of nested functions.
- **Model works but refresh or visuals feel sluggish?** Phase 5 covers the VertiPaq engine and the
  performance habits that follow from how it actually stores and scans your data.

## The phases

1. **[Row Context vs Filter Context](01-row-context-vs-filter-context.md)** 🔴 - the two invisible frames every DAX formula runs inside, and why the same measure reads differently depending on which one it's in.
2. **[CALCULATE and Context Transition](02-calculate-and-context-transition.md)** 🔴 - the function you reach for to rewrite filter context, and the automatic row-to-filter conversion that trips everyone up first.
3. **[Common DAX Patterns](03-common-dax-patterns-running-totals-yoy-ranking-t.md)** 🔴 - running totals, year-over-year, ranking, and top-N: the formulas you'll actually reuse, built from context you now understand instead of copy-pasted blind.
4. **[Variables, Debugging & Readable DAX](04-variables-debugging-and-readable-dax.md)** 🔴 - `VAR`/`RETURN`, how to actually debug a wrong number, and writing measures your future self can still read.
5. **[Performance & the VertiPaq Engine](05-performance-and-the-vertipaq-engine.md)** 🔴 - how Power BI actually stores your data in memory, and why that explains which DAX is fast and which DAX quietly isn't.

> This guide stays inside DAX itself - modeling choices like star schema design and relationship types
> live in [Power BI From Zero](/guides/power-bi-from-zero) and [Star Schema Explained](/guides/star-schema-explained).


---

# Row Context vs Filter Context

You've written measures. `SUM`, `AVERAGE`, a `DIVIDE` or two, maybe a `CALCULATE` you copied from a forum post and it worked. Then one day the same measure gives you 4,200 on a card and 380 in a matrix cell, and nothing about the formula changed. That's not a bug in Power BI. It's DAX telling you that a formula's answer depends on *where* it's being asked, not just *what* it says. This phase gives you the mental model that makes that stop being surprising.

## Two questions DAX asks before it computes anything

Every time DAX evaluates a formula, two separate questions get answered first, and the formula's result depends on both:

1. **"Which row am I currently standing on?"** - this is row context.
2. **"Which rows in the model are currently visible at all?"** - this is filter context.

These are not two names for the same thing. They answer different questions, they get created by different mechanisms, and - this is the part that trips almost everyone up - **row context does not automatically turn into filter context.** You have to ask for that explicitly (that's the whole subject of phase 2, `CALCULATE` and context transition). For now, treat them as two independent lenses DAX looks through, and learn to ask "what context am I in right now?" before you read any formula.

## Row context: "which row am I on"

**What it actually is.** Row context exists whenever DAX is walking through a table one row at a time and evaluating an expression for each row individually. Think of it as DAX standing at a specific row and being able to reach sideways into that row's other columns, like a spreadsheet formula that says "this cell, same row."

**Where it comes from.** Two places create row context:

- **Calculated columns.** A calculated column is defined once but evaluated once *per row* of the table it lives in. Every row gets its own row context automatically.
- **Iterator functions** - the ones ending in `X` (`SUMX`, `AVERAGEX`, `RANKX`, `MAXX`), plus a few like `FILTER` that iterate without the `X`. These functions take a table and an expression, and walk the table row by row, creating a fresh row context for each one, evaluating the expression there, then combining the results.

Here's row context in its simplest form, a calculated column on a `Sales` table:

```dax
Line Total = Sales[Quantity] * Sales[Unit Price]
```

There's no `SUM`, no filter, nothing aggregating. For every single row, DAX asks "what's `Quantity` *on this row* times `Unit Price` *on this row*?" That "on this row" is row context doing the work. Change the row, the referenced values change with it, automatically, because you're always reaching into the row you're currently standing on.

Iterators give you the same row-by-row walk, but inside a measure instead of a column, and the result gets collapsed into a single number at the end:

```dax
Total Revenue =
SUMX (
    Sales,
    Sales[Quantity] * Sales[Unit Price]
)
```

Read `SUMX` as two jobs stapled together: "walk `Sales` one row at a time (row context), compute `Quantity * Unit Price` on each row, then add up everything you got." It's the calculated-column formula above, except instead of storing 10 million individual results in a column, DAX computes each one, adds it to a running total, and throws it away. This matters later for performance (phase 5) - a calculated column pays the storage cost once at refresh, `SUMX` pays a small compute cost every time the measure runs - but right now the point is just: **`SUMX`'s second argument runs once per row, inside row context, exactly like a calculated column would.**

## Filter context: "which rows are even visible"

**What it actually is.** Filter context is the set of filters currently narrowing down the whole model before any calculation runs. It answers "out of every row in every table, which ones am I even allowed to see right now?" A measure doesn't see your whole `Sales` table - it sees whatever slice survived the filter context around it.

**Where it comes from.** Filter context is built up from everything surrounding a measure's evaluation:

- Slicers and filter panes the report user has touched.
- The row and column headers of a matrix or table visual - each cell has its own filter context, built from that row's and column's labels.
- Page-level and report-level filters.
- `CALCULATE`'s filter arguments, which can add, replace, or remove filters (that's the entire subject of phase 2).

A plain `SUM` measure has no row context of its own - it doesn't walk anything - it just asks "of the rows currently visible, add up this column":

```dax
Total Revenue (Simple) = SUM ( Sales[Revenue] )
```

Drop that measure on a card with no filters, and it sums every row in `Sales` - filter context is "everything." Drop the exact same measure into a matrix with `Region` on rows and `Year` on columns, and each cell recalculates it with a *different* filter context: the cell for "West / 2025" only sees rows where `Region = West` and `Year = 2025`. Same formula, eleven different numbers, because filter context changed eleven times - once per cell - while the formula never changed once.

This is the answer to "why does my measure give a different number in different places." It isn't giving different answers to the same question. Each visual, each cell, each slicer combination is asking a genuinely different question - "what's the total *given this filter context*" - and `SUM` is answering exactly the question it was asked, every time.

## Watching both at once

Here's a formula that uses row context and filter context in the same breath, so you can see them as the two separate things they are:

```dax
Avg Line Value =
AVERAGEX (
    Sales,
    Sales[Quantity] * Sales[Unit Price]
)
```

`AVERAGEX` walks `Sales` row by row (row context) computing `Quantity * Unit Price` for each row - but *which* rows of `Sales` it walks over is decided by whatever filter context is active when the measure runs. Put this on a card with no filters: it walks every row in the table. Put it in a matrix cell for "West / 2025": it only walks the rows that survive that filter context, then averages *those*. Filter context decides the guest list; row context is what happens to each guest once they're in the room.

| | Created by | Answers | Lives inside |
|---|---|---|---|
| **Row context** | Calculated columns, iterators (`SUMX`, `FILTER`, `RANKX`, ...) | "What's on this row?" | A single row, one at a time |
| **Filter context** | Slicers, visual headers, page/report filters, `CALCULATE` | "Which rows are visible right now?" | The whole table, as a filtered slice |

## Why this is the foundation for everything else

Nearly every DAX surprise you'll hit traces back to mixing these two up:

- **"My calculated column doesn't respond to slicers."** Correct - a calculated column is computed once at refresh time, using row context only. It has no idea a filter context will exist later, because filter context comes from visuals, and visuals don't exist yet when the column is calculated. This is also why calculated columns and measures aren't interchangeable: a column bakes a value into storage per row; a measure recomputes live, in whatever filter context it's asked from.
- **"Why does referencing `Sales[Quantity]` on its own blow up inside a measure?"** Mirror-image reason - a measure has no row context of its own, so a bare column reference has no "current row" to read, which is why you have to wrap it in an aggregator (`SUM`) or an iterator. A measure only ever works through filter context, until something explicitly converts a row context into one.

That conversion - taking row context and turning it into filter context so a measure suddenly *can* see "this row" - is what `CALCULATE` does, and it's arguably the single most important mechanism in the entire language. That's next.

### Check yourself

```quiz
[
  {
    "q": "A calculated column defined as `Sales[Quantity] * Sales[Unit Price]` doesn't change when you add a slicer to the report. Why not?",
    "choices": [
      "It's computed once at refresh time using row context only, before any filter context from visuals exists",
      "Calculated columns are secretly measures and just need CALCULATE added",
      "Slicers only affect columns that use SUMX, not plain multiplication",
      "The column needs an explicit filter context in its formula to respond to slicers"
    ],
    "answer": 0,
    "explain": "Calculated columns are baked into storage once at refresh, using only row context - filter context comes from visuals that don't exist yet at that point."
  },
  {
    "q": "You put `SUM(Sales[Revenue])` on a card (no filters) and get 4,200. You drop the same measure into a matrix with Region on rows, and the 'West' cell shows 380. What changed?",
    "choices": [
      "The formula silently changed between the two visuals",
      "Nothing changed about the formula - the filter context around it changed, so it's answering a different question each time",
      "The matrix cell is using row context instead of filter context",
      "Power BI cached a stale value for the card"
    ],
    "answer": 1,
    "explain": "SUM always answers 'total of what's currently visible.' A card with no filters sees everything; a matrix cell sees only the rows its row/column headers filtered down to - same formula, different filter context, different number."
  },
  {
    "q": "In `SUMX(Sales, Sales[Quantity] * Sales[Unit Price])`, what decides *which* rows of `Sales` get walked?",
    "choices": [
      "Row context, since SUMX creates a fresh row context per row",
      "Whatever filter context is active when the measure runs - row context only governs what happens once inside a given row",
      "The order the rows appear in the table",
      "SUMX always walks the entire table regardless of filters"
    ],
    "answer": 1,
    "explain": "Filter context decides the guest list (which rows survive to be walked); row context is what SUMX does to each row once it's in the room - the two jobs are separate even inside one formula."
  }
]
```


---

# CALCULATE and Context Transition

Phase 1 left you with a working model of row context and filter context: row context is "which row am I standing on," filter context is "which rows survived the slicers, the visual, and the filter pane." CALCULATE is the one function that lets you reach into filter context and change it mid-formula. It is the single most powerful function in DAX, and also the one that produces the most "wait, why is this number different" moments. Once you can predict what CALCULATE does before you run it, most of the mystery in DAX disappears.

## What CALCULATE actually is

**The mental model first.** Every other DAX function evaluates inside the filter context it's handed. CALCULATE is different: it evaluates an expression *after* modifying the filter context around it. Think of filter context as a set of instructions taped to the wall - "only look at rows where Region = West, Year = 2024." CALCULATE lets a measure walk up, tear off one of those instructions, tape up a new one, and only then read the wall.

```dax
CALCULATE(<expression>, <filter1>, <filter2>, ...)
```

The first argument is what to compute - almost always another measure or an aggregation like `SUM(Sales[Amount])`. Everything after that is a filter argument, and filter arguments are where the actual power (and the actual confusion) lives.

## Filter arguments: replace, don't intersect

The single most important thing to internalize about CALCULATE's filter arguments is this:

> **A filter argument does not narrow the existing filter on that column - it replaces it entirely.**

This trips people up constantly because it's *not* how an `AND` in a WHERE clause behaves. Watch it happen:

```dax
Sales West =
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Region] = "West"
)
```

Drop this measure into a table visual sliced by `Sales[Region]`, and every single row shows the same number - the total West sales - regardless of what region that row of the table represents. That's not a bug. `Sales[Region] = "West"` didn't add to the visual's existing filter on Region; it replaced it outright. The row for "East" asked "what are West sales," and CALCULATE answered straight, exactly as asked.

This is exactly why CALCULATE is powerful: it's how you build a measure that deliberately ignores what the user clicked. It's also exactly why it's dangerous: if you forget this rule, you write measures that silently ignore slicers you expected them to respect.

## Context transition: the part nobody tells you clearly

Here's the idea that separates people who can write CALCULATE from people who can *predict* what it does: **CALCULATE doesn't just add filters - it also converts any row context in scope into an equivalent filter context.** This is called context transition, and it only happens because of CALCULATE (or a handful of things that implicitly wrap CALCULATE, like measure references inside `SUMX`).

Recall from phase 1: row context exists inside iterators like `SUMX` and inside calculated columns - it's "I'm standing on this one row." Filter context is what a measure actually reads. Normally those two never talk to each other. CALCULATE is the bridge.

Here's the pattern that makes this concrete - a calculated column that calls a measure:

```dax
-- Calculated column on the Products table
Product Total Sales = CALCULATE(SUM(Sales[Amount]))
```

You're inside a calculated column, so you have row context - you're standing on one product row, say "Blue Widget." `SUM(Sales[Amount])` has no idea what a "row context" is; it only reads filter context, and by default a calculated column has *no* filter context at all (it would sum the whole Sales table for every single row, giving every product the grand total). But because you wrapped it in CALCULATE, DAX performs context transition: it looks at the row you're standing on, finds every column value on that row ("Blue Widget"), and injects an equivalent filter - `Products[Name] = "Blue Widget"` - into filter context before evaluating. That filter sits on the Products table, and the Products -> Sales relationship carries it across to restrict `SUM(Sales[Amount])` to that product's rows. The row you were standing on becomes a filter. That's the whole trick.

Without the `CALCULATE` wrapper, `SUM(Sales[Amount])` in that same column would just be the grand total repeated on every row, because there'd be no filter context to shrink it. This is why you'll see experienced DAX authors reach for `CALCULATE` even around something that looks like it needs no filter arguments at all - they're not filtering, they're transitioning.

The same thing happens inside `SUMX` and other iterators whenever the expression being iterated calls a measure:

```dax
Total With Tax =
SUMX(
    Sales,
    Sales[Amount] * (1 + RELATED(Products[TaxRate]))
)
```

No context transition needed here because `RELATED` and the multiplication just read row context directly - no measure call, no CALCULATE, no transition. But the moment you swap in a measure reference:

```dax
Total With Tax v2 =
SUMX(
    Sales,
    [Unit Tax Measure]   -- implicitly wrapped in CALCULATE
)
```

Every call to `[Unit Tax Measure]` inside that iteration silently performs context transition, turning "the row I'm on" into "a one-row filter," before the measure evaluates. This is invisible in the formula bar and is one of the most common sources of performance surprises (a transition per row, times a million rows) and correctness surprises (the measure now sees a filter it didn't expect) once you get to phase 5 on performance.

## ALL, ALLEXCEPT, ALLSELECTED: controlling what survives

CALCULATE's filter arguments aren't limited to conditions like `Sales[Region] = "West"`. They can be *table-returning functions* that remove filters entirely, which is how you build "percent of total" and "compare to the unfiltered whole" measures.

**`ALL(table_or_column)`** removes every filter on the given table or column, ignoring slicers, visual filters, everything.

```dax
Pct of All Sales =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)
```

The denominator recalculates total sales as if no filter existed on any `Sales` column - the grand total for this single-table model - while the numerator still respects whatever's currently filtered. Divide the two and you get a real percent-of-total. One caveat for star schemas: `ALL(Sales)` only clears filters that sit on Sales's own columns; a slicer on a related dimension table like `Date` still reaches Sales through the relationship, so you'd add `ALL` on that table too for a true grand total.

**`ALLEXCEPT(table, column1, column2, ...)`** is the scalpel version: remove all filters on the table *except* the ones on the columns you name.

```dax
Pct of Region =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Region]))
)
```

This clears filters on Product, Date, Customer - everything - but keeps the Region filter standing. So a row for "West / Blue Widget" gets compared against the total for all of West, not the grand total. `ALLEXCEPT` is `ALL` with an escape hatch for the one or two columns you still want respected.

**`ALLSELECTED(table_or_column)`** is the one that confuses people because it sounds like `ALL` but behaves differently: it removes filters coming from *inside the visual* (like a filter on a column added within the chart itself) while still respecting filters from outside the visual - slicers, the filter pane, page filters. It answers "what does the rest of what the user selected look like," not "what does everything look like." Use it for things like "percent of the currently sliced total" in a table that itself breaks that total down further - so a subtotal row correctly sums to 100% of what's visibly selected, not 100% of the entire database.

A rough way to keep these straight: `ALL` = "pretend nothing is filtered, anywhere." `ALLEXCEPT` = "pretend nothing is filtered, except these columns." `ALLSELECTED` = "pretend this visual isn't adding its own filters, but respect everything the user picked outside it."

## Putting it together: a real pattern

A common real request - "sales for the current row's region, but always compare against last year regardless of what's in the visual" - combines everything above:

```dax
Region Sales LY =
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date]),
    ALLEXCEPT(Sales, Sales[Region])
)
```

Read it the way CALCULATE actually executes it: start from the current filter context, replace the date filter with "same period, one year back," clear every other filter except Region, then sum. Nothing here is magic once you separate "what filter context currently exists," "which filter arguments replace pieces of it," and whether a context transition is quietly happening because you're inside an iterator or a calculated column.

## Recap

1. **CALCULATE modifies filter context and then evaluates an expression inside the modified context.**
2. **Filter arguments replace the existing filter on that column, they don't intersect with it** - this is the single biggest source of "why is every row the same number" bugs.
3. **Context transition** turns row context into filter context, and happens automatically inside CALCULATE and inside any measure call sitting in an iterator or calculated column - even with no visible filter arguments.
4. **`ALL`** clears filters entirely, **`ALLEXCEPT`** clears all but the named columns, **`ALLSELECTED`** clears only what the visual itself added, keeping outside selections.
5. Predicting a CALCULATE result means tracking three things at once: what filter context existed before, what the filter arguments replace, and whether a context transition just happened underneath you.

## Quick check

Test yourself on the two ideas that cause the most confusion here - that filter arguments replace rather than intersect, and that context transition turns a row into a filter:

```quiz
[
  {
    "q": "A table visual is sliced by `Sales[Region]`. You add a measure `CALCULATE(SUM(Sales[Amount]), Sales[Region] = \"West\")`. What does the East row show?",
    "choices": [
      "Zero, because East doesn't match \"West\"",
      "West's total sales, because the filter argument replaced the visual's Region filter instead of intersecting with it",
      "East's total sales, unaffected, because CALCULATE only adds filters on columns not already filtered",
      "An error, because you can't filter a column that's already sliced by the visual"
    ],
    "answer": 1,
    "explain": "A CALCULATE filter argument overwrites the existing filter on that column rather than combining with it, so every row - including East - ends up asking for West's total."
  },
  {
    "q": "A calculated column on the Products table is `CALCULATE(SUM(Sales[Amount]))`, with no filter arguments at all. Why doesn't every product just show the grand total?",
    "choices": [
      "Calculated columns automatically filter by whatever column they're defined on",
      "CALCULATE performs context transition, converting the row you're standing on into a filter that restricts SUM to that product",
      "SUM ignores rows from other products by default, with or without CALCULATE",
      "It does show the grand total on every row - that's expected for calculated columns"
    ],
    "answer": 1,
    "explain": "CALCULATE always triggers context transition: it takes every column value on the current row and injects it as a filter before evaluating, which is why wrapping SUM in CALCULATE is enough to scope it per-row even with zero filter arguments."
  },
  {
    "q": "Inside `SUMX(Sales, Sales[Amount] * RELATED(Products[TaxRate]))`, does a context transition happen on each row?",
    "choices": [
      "Yes, every iterator triggers context transition regardless of what's inside it",
      "No, because RELATED and the multiplication read row context directly - transition only happens when a measure call sits inside the iteration",
      "Yes, because SUMX always wraps its expression in CALCULATE",
      "No, because context transition only applies to calculated columns, never to iterators"
    ],
    "answer": 1,
    "explain": "RELATED just follows a relationship using row context, no measure call involved, so nothing triggers CALCULATE's context transition - swap in a measure reference instead and every row would silently transition."
  }
]
```


---

# Common DAX Patterns (running totals, YoY, ranking, top-N)

By now you've internalized the two ideas that matter: a measure lives inside a filter context, and `CALCULATE` is how you rewrite that context before your expression runs. If you're shaky on either, go back to [Phase 1: Row Context vs Filter Context](01-row-context-vs-filter-context.md) and [Phase 2: CALCULATE and Context Transition](02-calculate-and-context-transition.md) first - everything here assumes you can read a `CALCULATE` call and say, in a sentence, what filter it just replaced.

Here's the payoff for that work: the "advanced DAX patterns" everyone treats as a checklist to memorize - running totals, year-over-year, ranking, top-N - are not four unrelated tricks. Each one is `CALCULATE` paired with one of a small number of table functions (`ALL`, `ALLEXCEPT`, `FILTER`, `TOPN`) or an iterator (`RANKX`). Learn to read what each function does to the *candidate table* it's handed, and you stop memorizing patterns and start deriving them.

## Running totals: widening the filter, not resetting it

**What it actually is.** A running total needs a filter context that says "every date up to and including this one" - not "just this one," which is what a table or matrix visual normally gives you.

```dax
Cumulative Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)
```

Read this from the inside out, because that's the order it evaluates:

1. `ALL('Date'[Date])` hands back *every* date in the table, ignoring whatever filter context already narrowed it down. This is the step people skip and then can't figure out why their "running total" shows one day's sales instead of a cumulative number - without `ALL`, the table `FILTER` is scanning is already restricted to the single date the visual put you on, so `<= MAX(...)` just gives that same one date back.
2. `MAX('Date'[Date])` still sees the *original* filter context (it's not wrapped in `CALCULATE`), so inside a matrix it correctly returns "the date this row represents."
3. `FILTER` walks the full, unfiltered date table and keeps only the dates on or before that row's date.
4. `CALCULATE` takes that filtered table and uses it to replace the date filter, then re-evaluates `[Total Sales]` underneath it.

The result: at the March 15th row, this measure sums every sale from the beginning of the table through March 15th - a genuine running total, one that keeps accumulating across year boundaries, unlike `TOTALYTD` which deliberately resets every January 1st. That difference is the whole reason this pattern exists as its own thing, distinct from the time intelligence functions you already know: `TOTALYTD` answers "how much so far *this year*," this pattern answers "how much *ever*."

## Year-over-year: the same move, applied to a comparison

You already know `CALCULATE` plus `SAMEPERIODLASTYEAR` shifts a date filter back a year. The pattern worth internalizing here is what you do *with* that shifted measure - because "YoY" almost always means the growth percentage, not the prior-year number by itself:

```dax
Sales PY =
CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )

Sales YoY % =
DIVIDE( [Total Sales] - [Sales PY], [Sales PY] )
```

Nothing new mechanically - `SAMEPERIODLASTYEAR` is a date-table generator, same category as the `ALL('Date'[Date])` from the running total above, just narrower. The lesson to carry forward: any "compare X to some other slice of X" measure is the same three-line shape - build the comparison measure with `CALCULATE` and a filter generator, then `DIVIDE` the difference by the comparison value. Month-over-month, quarter-over-quarter, this-region-vs-all-regions - it's the identical skeleton with a different filter argument.

## Ranking: row context, one candidate at a time

**What it actually is.** `RANKX` takes a table and an expression, and for *each row* of that table, evaluates the expression and counts how many other rows scored higher (or lower, if you ask for ascending). It's an iterator, like `SUMX` - it creates row context on the table you give it, one row at a time.

```dax
Product Rank by Sales =
RANKX(
    ALL('Product'[Product Name]),
    [Total Sales]
)
```

Two things trip people up here, and both are the same underlying idea:

**Why `ALL` is doing real work again.** If a matrix visual has already filtered you down to one product, `RANKX` only has one row to rank - it'll always return 1, for everyone. `ALL('Product'[Product Name])` gives `RANKX` the *full* universe of products to compare against, regardless of what the current row's filter narrowed things to.

**Why `[Total Sales]` recalculates per candidate.** For every product `RANKX` walks past, it puts that one product into row context, then evaluates `[Total Sales]` - and because `[Total Sales]` is a measure, evaluating it inside a fresh row context triggers context transition (Phase 2): that single product name becomes a one-item filter context, and `[Total Sales]` computes that product's total from scratch. `RANKX` does this once per candidate row, collects every result, and only then figures out where the *current* row's value lands among them. This is exactly the row-context-to-filter-context handoff you learned to watch for - `RANKX` is just the function that makes you do it dozens or thousands of times in one call.

## Top-N: the same ranking idea, but returning a table

`RANKX` answers "where does this row rank" - a number, one per row, useful for a rank column or for filtering ("show me rank <= 5"). `TOPN` answers a related but different question - "give me the N highest-scoring rows as a table" - which makes it the right tool when you want a fixed top-N total that ignores whatever a slicer elsewhere on the page is doing:

```dax
Top 5 Products Sales =
CALCULATE(
    [Total Sales],
    TOPN(5, ALL('Product'[Product Name]), [Total Sales], DESC)
)
```

`TOPN` walks `ALL('Product'[Product Name])` the same way `RANKX` did, scoring each product by `[Total Sales]`, and hands `CALCULATE` back a table containing only the five highest-scoring rows. `CALCULATE` then uses that five-row table as the new product filter and re-evaluates `[Total Sales]` underneath it - so this measure sums the top five products' sales, on a card, in a total row, anywhere - even sitting next to a slicer someone has set to a single, unrelated product. It never lies about which five it means, because `ALL` refuses to let the surrounding filter context sneak into the candidate list in the first place. (One edge worth knowing: if two products tie at the fifth-place score, `TOPN` returns *all* the tied rows rather than picking one, so you can occasionally get six or more - it widens rather than silently dropping a real tie to force the count to exactly five.)

## The pattern behind the patterns

All four of these boil down to answering one question before you write a line of DAX: **what table should `CALCULATE` (or an iterator) be looking at, and does the existing filter context need to be widened, replaced, or ranked before I use it?**

- Running total: widen the date filter to "everything up to here" with `ALL` + `FILTER`.
- Year-over-year: replace the date filter with a shifted one, then `DIVIDE` two calls of the same measure.
- Ranking: widen the candidate table with `ALL`, then let `RANKX` re-run your measure once per candidate through context transition.
- Top-N: widen the candidate table with `ALL`, let `TOPN` keep only the best rows, then `CALCULATE` on what's left.

Every advanced pattern you'll meet after this - "% of parent category," "new customers this month," "average of the last 3 non-blank months" - is built from this same short list of moves. The functions change; the reasoning doesn't.

## Quick check

Test yourself on the move that shows up in all four patterns - widening a table with `ALL` before `CALCULATE` or an iterator gets to use it:

```quiz
[
  {
    "q": "In the Cumulative Sales measure, what would happen if you dropped ALL('Date'[Date]) and wrote FILTER('Date'[Date], 'Date'[Date] <= MAX('Date'[Date])) instead?",
    "choices": [
      "The measure would still compute a correct running total, just slower",
      "FILTER would walk the table the visual already narrowed to one date, so <= MAX would just hand back that same single date - no different from the plain total",
      "DAX would throw a circular reference error at that row",
      "The measure would sum every date in the table regardless of row context"
    ],
    "answer": 1,
    "explain": "Without ALL, FILTER never sees the full date table - it only sees what the visual already filtered down to, so the 'running' part disappears and you get the same number as a plain total."
  },
  {
    "q": "Why does RANKX(('Product'[Product Name]), [Total Sales]) - without wrapping the table in ALL - return 1 for every product in a matrix visual?",
    "choices": [
      "RANKX only ranks correctly in ascending order",
      "Each row's filter context has already narrowed the table to one product, so RANKX only ever has that single candidate to compare against",
      "RANKX needs ALLEXCEPT, not ALL, to rank correctly",
      "[Total Sales] can't be evaluated inside RANKX without first being stored in a variable"
    ],
    "answer": 1,
    "explain": "The visual's filter context narrows the candidate table before RANKX ever sees it, so without ALL widening it back to every product, each row is ranked against a field of one - itself."
  },
  {
    "q": "What's the real difference between RANKX and TOPN, based on how this phase used them?",
    "choices": [
      "RANKX returns a table of winning rows; TOPN returns a rank number per row",
      "RANKX returns one rank number per row; TOPN returns a table of the top-scoring rows, which CALCULATE can then use to filter",
      "They're interchangeable - either can be wrapped in CALCULATE to build a top-5 measure",
      "RANKX needs ALL to work but TOPN never does"
    ],
    "answer": 1,
    "explain": "RANKX scores rows one at a time and hands back a number; TOPN hands back a whole table of winners, which is exactly the shape CALCULATE needs to use as a new filter for the top-N pattern."
  }
]
```


---

# Variables, Debugging & Readable DAX

By now you've written measures with two or three nested `CALCULATE` calls, maybe a `FILTER` inside a `FILTER`, and you've had the experience of staring at your own formula from two weeks ago wondering what it does. That's not a you problem. DAX without variables reads like a sentence with no punctuation - every function call is buried inside the next one, and the only way to know what a sub-expression evaluates to is to re-derive it in your head.

`VAR` and `RETURN` fix this, but they're not just a readability nicety bolted onto the language. They change *how* your formula evaluates, which means they're also your main debugging tool and, often, a real performance win. Understanding what a variable actually *is* in DAX - not just how to type one - is what this phase is about.

## What a variable actually is

**What it actually is.** A `VAR` names the result of an expression, evaluated once, inside the context that exists at the point where the variable block runs. `RETURN` gives back the final value. The syntax:

```dax
Profit Margin =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR Margin = DIVIDE(TotalRevenue - TotalCost, TotalRevenue)
RETURN
    Margin
```

That's a straight rewrite of what you'd otherwise cram into one line - `DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue]))` - but now every piece has a name, and you can read the formula top to bottom like a recipe instead of unpacking parentheses from the inside out.

**Why this exists.** Two reasons, and both matter.

First, **evaluate-once**. `SUM(Sales[Revenue])` shows up twice in the un-varred version above. Without a variable, DAX's engine evaluates that expression twice - once for the subtraction, once for the division - re-scanning the same filtered table both times. Assign it to `VAR TotalRevenue` and it's computed once, stored, and reused. On a small model you won't feel it. On a large fact table with an expensive `CALCULATE` repeated three times in one measure, this is a real, measurable speedup - sometimes the difference between a report that feels instant and one that spins.

Second, **a variable's context is frozen at the point it's defined**. This is the part that trips people up, so sit with it: once `VAR TotalRevenue = SUM(Sales[Revenue])` has been evaluated, `TotalRevenue` is just a number - it does not change if something later in the formula shifts the filter context. It doesn't re-evaluate per row, it doesn't get affected by a `CALCULATE` that comes after it in the `RETURN` clause. It's a value, not a live formula.

Watch what that buys you:

```dax
Sales vs Category Total =
VAR CurrentSales = SUM(Sales[Revenue])
VAR CategoryTotal =
    CALCULATE(
        SUM(Sales[Revenue]),
        ALL(Product[Product Name])
    )
RETURN
    CurrentSales - CategoryTotal
```

`CurrentSales` locks in the revenue for whatever row context or slicer selection is active *right now* - a specific product, say. `CategoryTotal` then throws that product-level filter away with `ALL(Product[Product Name])` to sum revenue across every product in the current category - assuming you're viewing this inside a category, like a Category > Product matrix; with no category in context (a flat product list), `ALL` strips the product name and you get the grand total across all products instead. Either way, it's a total, not an average - the name says what it computes. Because `CurrentSales` was already computed and frozen before `CategoryTotal`'s `CALCULATE` runs, the two don't interfere with each other. Without variables you'd need to be very careful about evaluation order and re-derive the first `SUM` from scratch a second time to get the same safety.

📝 **Terminology.** The precise way to say this: a `VAR` is evaluated in the context where it's *defined*, not where it's *used* - unlike a plain sub-expression, which the engine re-evaluates wherever it textually appears, a `VAR` is computed once and behaves like a constant for the rest of the formula.

## Using VAR as your debugger

Here's the practical payoff. DAX has no breakpoints, no step-through debugger, no `console.log`. What it has is `RETURN` - and that means you can turn any variable into a temporary probe.

Say this measure is giving you a number that looks wrong:

```dax
YoY Growth % =
DIVIDE(
    [Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date])),
    CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
)
```

You don't know which half is wrong: this year's number, last year's number, or the `DIVIDE`. Rewrite it with variables and expose them one at a time:

```dax
YoY Growth % (debug) =
VAR CurrentRevenue = [Total Revenue]
VAR PriorRevenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
VAR Growth = DIVIDE(CurrentRevenue - PriorRevenue, PriorRevenue)
RETURN
    PriorRevenue -- <- temporarily return just this
```

Drop that measure into a table visual next to your date column. Now you're looking at `PriorRevenue` in isolation, next to the row it belongs to, for every date in the table at once - something no debugger built for procedural code even offers, because DAX's "loop" is really the visual's rows doing the iterating for you. If `PriorRevenue` looks right, swap the `RETURN` to `CurrentRevenue` and check that. If both look right, the bug is in `Growth` itself, probably a sign flip or a `DIVIDE` argument in the wrong order. This "return one variable at a time" technique is the single most useful debugging habit in DAX - it costs one line to change and tells you exactly which layer is lying to you.

⚠️ **The trap.** Don't leave debug `RETURN` swaps in production measures. It's easy to fix the bug, confirm `Growth` is now correct, and forget to change `RETURN PriorRevenue` back to `RETURN Growth`. Ship the wrong `RETURN` line and the measure works in your test table but is silently wrong everywhere else. Grep your model for stray debug returns before you call a fix done.

💡 **Key point.** For anything past a two-line fix, Power BI Desktop's built-in DAX query view (added 2024) - or DAX Studio, the free external tool - lets you run the *whole* measure definition as a query and see the actual results, query plan, and row counts - worth knowing exists once your models get big enough that visual-probing gets slow. But `VAR`/`RETURN` swapping is the everyday tool, and it needs nothing installed.

## What makes DAX readable

Once you're using `VAR` for debugging, you get readability for free, but a few habits make the difference between "readable" and "readable if you already know what it does":

- **Name variables for what they mean, not what they compute.** `VAR PriorYearRevenue` beats `VAR X1`. Six months from now you want the name to explain itself without re-reading the formula.
- **One idea per variable.** Resist cramming a `CALCULATE` and a `FILTER` and an `IF` into a single `VAR` line just because you can. Break it into two named steps even if it "could" be one expression - the extra line costs nothing at runtime and saves the next reader real time.
- **Put the final `RETURN` last, and keep it simple.** If your `RETURN` line is itself a five-function nested expression, you've just moved the unreadable part to the bottom instead of removing it. The `RETURN` should read like a sentence: `RETURN IF(HasSales, Growth, BLANK())`.
- **Comments earn their keep on the "why," not the "what."** `-- exclude returns, per finance's Q3 definition` tells a future reader something the code can't. `-- sum of revenue` next to `SUM(Sales[Revenue])` tells them nothing they can't already read.

None of this changes what the measure computes. It changes whether you - or a teammate - can trust it, fix it, or extend it without redoing the reasoning from scratch. That's not a style preference; on a model with fifty measures, it's the difference between a model people trust and one everybody quietly routes around.

## Quick check

Test yourself on the idea that makes VAR more than a style choice - that a variable's value is frozen the moment it's computed:

```quiz
[
  {
    "q": "In the Sales vs Category Total measure, VAR CurrentSales = SUM(Sales[Revenue]) is defined before VAR CategoryTotal, which uses CALCULATE with ALL to strip the product filter. What happens to CurrentSales when CategoryTotal's CALCULATE runs?",
    "choices": [
      "Nothing - CurrentSales was already evaluated and frozen as a plain number before CategoryTotal's CALCULATE runs",
      "CurrentSales also loses its product filter, since CALCULATE changes context for the whole measure",
      "CurrentSales is silently recalculated to match the new filter context",
      "The measure errors out because both variables reference SUM(Sales[Revenue])"
    ],
    "answer": 0,
    "explain": "A VAR's context is frozen at the point it's defined - it becomes a plain value, so a CALCULATE later in the formula can't reach back and change it."
  },
  {
    "q": "A YoY Growth % measure is giving a wrong number, and you don't know if the bug is in this year's revenue, last year's revenue, or the DIVIDE. What's the standard DAX way to isolate which part is broken?",
    "choices": [
      "Break the formula into named VARs, then temporarily change RETURN to expose one variable at a time in a table visual",
      "Wrap the whole expression in IFERROR to catch which piece fails",
      "Delete the CALCULATE calls one by one until the number looks right",
      "Rewrite the measure in Power Query instead, since DAX can't be debugged"
    ],
    "answer": 0,
    "explain": "DAX has no breakpoints or step-through debugger - swapping RETURN to expose one VAR at a time in a visual is how you see each sub-expression's value in isolation."
  },
  {
    "q": "You temporarily set RETURN PriorRevenue to check that value, confirm it's correct, and move on to checking Growth. What's the easy mistake that ships a silently broken measure?",
    "choices": [
      "Forgetting to change RETURN back to the real result before publishing, so the measure keeps returning the debug value",
      "Forgetting to delete the VAR declarations before publishing",
      "Forgetting to rename the measure so it doesn't collide with the original",
      "Forgetting to remove SAMEPERIODLASTYEAR from the formula"
    ],
    "answer": 0,
    "explain": "The VARs themselves are harmless - the risk is leaving RETURN pointed at a debug variable, which makes the measure return the wrong thing everywhere it's used, not just in your test table."
  }
]
```


---

# Performance & the VertiPaq Engine

You've written correct DAX. Row context, filter context, `CALCULATE` - you get it now. Then one day a
measure that looked identical to a dozen others takes eleven seconds to render a table visual, and you
have no idea why. Nothing in the formula is wrong. The *logic* is fine. What's different is how much work
Power BI has to do to answer it, and that work is decided by something you haven't looked at yet: how
your data actually sits in memory.

This phase is that missing piece. Not a list of "best practices" to memorize and hope for - the actual
mechanism, so that when you look at a DAX formula you can predict roughly how expensive it'll be, the same
way phase 1 let you predict what context a formula runs in.

## The mental model: VertiPaq doesn't think in rows

**What it actually is.** VertiPaq is the in-memory engine Power BI uses to store your model (it's the
same engine under Analysis Services and Excel's Power Pivot). The single fact that explains almost
everything else in this phase: **VertiPaq stores data by column, not by row.**

A normal database row looks like a record - `OrderID, Date, CustomerID, Product, Quantity, Amount` - all
sitting together on disk, because you usually fetch one whole order at a time. VertiPaq throws that
layout away. Instead it stores one long, separate list for *every column*: all the dates in one contiguous
block, all the amounts in another, all the customer IDs in another. A table with a million rows and eight
columns isn't one million-row structure - it's eight independent million-value lists that happen to line
up by position.

**Why this exists.** Analytical queries almost never want "the whole row." A visual asking for `SUM(Amount)
by Month` only touches two columns out of eight. If the engine stored data row-by-row, it would still have
to stream every byte of every row past to pull out just those two columns. Storing by column means it
reads *only* the columns the query actually needs, and skips the rest entirely. That's the first reason
DAX can be fast: most queries only pay for a slice of your table, not the whole thing.

## Compression: why a column of 1s and 2s is nearly free

Columnar storage on its own is a decent trick. What makes VertiPaq genuinely fast is what it does *to*
each column once it's isolated: heavy compression, and the technique depends on how many distinct values
the column has.

📝 **Terminology.** **Cardinality** is the number of *distinct* values in a column. A `Gender` column has
cardinality 2. A `TransactionID` column has cardinality equal to the row count - every value is unique.
Cardinality is the single biggest lever on model size and query speed, and you'll see it again and again
below.

VertiPaq's main trick is **dictionary encoding**. For a low-cardinality column like `Country`, it builds a
small dictionary (`0 = "USA"`, `1 = "Germany"`, `2 = "Japan"`, ...) once, then stores the column itself as
a compact list of small integers pointing into that dictionary, instead of repeating the text a million
times. A column with 5 distinct values compresses to almost nothing, no matter how many rows the table
has - VertiPaq only pays for the dictionary size and a tiny integer per row.

On top of that it applies **run-length encoding (RLE)** when a table is sorted so the same value repeats in
a run - `1,1,1,1,2,2,2,3,3` becomes "value 1 x4, value 2 x3, value 3 x2" - which is why a well-chosen sort
order at import time (something the engine tries automatically) can shrink a column further.

💡 **Key point.** This is *why* low cardinality is cheap and high cardinality is expensive - not a rule of
thumb, a direct consequence of the storage. A `TransactionID` column with a million unique values gets
almost no benefit from dictionary encoding (the dictionary is nearly as big as the data) and no benefit
from RLE (nothing repeats). A surrogate `DateKey` with 3,650 distinct values across ten years compresses
beautifully. This is exactly why star-schema modeling advice like "don't put a high-cardinality natural
key in your fact table if you can avoid it" isn't superstition - it's asking VertiPaq to do less work.

## Two engines, and where your formula actually runs

Every DAX query is actually handled by two different engines working together, and knowing which one is
doing the work explains a lot of "why is this slow" mysteries.

- **The Storage Engine (SE)** is VertiPaq itself: it scans compressed columns and answers simple requests
  - "sum this column, grouped by that one, filtered to this set of keys." It's written in native code,
  highly parallel, and very fast. It can also cache results.
- **The Formula Engine (FE)** is the part that understands DAX itself - row context, `CALCULATE`,
  iterators, anything with actual logic. It orchestrates: it asks the Storage Engine for the raw numbers
  it needs, then does whatever DAX-specific work the SE can't (row-by-row evaluation, complex branching).
  It's single-threaded and comparatively slow *per operation*.

A fast measure is one that pushes almost all the work down to SE and asks FE to do very little stitching.
A slow measure is one that forces FE to loop, row by row, doing something SE can't express as a single
scan.

```dax
-- Mostly SE: one scan, one aggregation, SE does almost everything
Total Sales := SUM(Sales[Amount])

-- Forces FE to iterate: one CALCULATE per row of Sales, evaluated by the Formula Engine
Total Sales Slow :=
SUMX(
    Sales,
    CALCULATE(SUM(Sales[Amount]))
)
```

Both formulas return the same number. The first is a single SE scan. The second asks FE to walk every row
of `Sales`, and for *each row* trigger a fresh `CALCULATE` (a context transition, from phase 2) that goes
back to SE for its own little scan. On a small table you'd never notice. On ten million rows, the second
version can be a thousand times slower for a result that was always just `SUM(Sales[Amount])`.

## Seeing it for yourself: reading the SE/FE split

You don't have to guess which engine is doing the work. Power BI's **Performance Analyzer** (View →
Performance Analyzer → Start Recording → interact with a visual) records the DAX query behind every visual
and its total duration - but it reports one number, not the split. To see how that time divides between
the two engines, copy the recorded query into **DAX Studio** (a free external tool) and run it with the
**Server Timings** tab on: that's what reports **Storage Engine time** vs **Formula Engine time**. That
split is the single most useful diagnostic in this whole phase.

| Visual | Total | SE time | FE time | Reading it |
|---|---|---|---|---|
| Card: `Total Sales` | 4 ms | 3 ms | 1 ms | Healthy - almost all SE |
| Table: `Total Sales Slow` by Product | 6,200 ms | 380 ms | 5,820 ms | FE-bound - the iterator is the problem |

When FE time dominates, the fix is almost never "make the hardware faster" - it's rewriting the measure so
more of the work can be pushed to SE. That's exactly what phase 3's patterns and phase 4's `VAR` habit were
doing all along, even before you had a name for why they helped: a `VAR` computed once and reused avoids
asking FE to redo the same context transition twice; `SUMX` only over a small aggregated table (not the
raw fact table) keeps the row-by-row work small.

## Performance habits that fall out of the model

None of these are arbitrary rules - each one is the mental model above, applied.

- **Prefer measures over calculated columns for anything that changes with filter context.** A calculated
  column is computed once at refresh and stored as a real column - so it's stored and scanned much like an
  imported one (though, being built after the initial compression pass, it often compresses less well), but
  it can't react to what's on the report, and a high-cardinality calculated column (like a
  row-by-row concatenation) bloats the model permanently. A measure is computed on demand and never stored,
  which is more query work but zero storage cost and always context-aware. Use a calculated column only
  when the value is genuinely fixed per row (a category flag, a fiscal quarter) - not as the default.
- **Avoid iterators over the raw fact table when an aggregation will do.** `SUMX(FactSales, ...)` walking
  ten million rows is FE-heavy; `SUMX` over a small pre-aggregated table (or plain `SUM`/`CALCULATE`) lets
  SE do the heavy lifting.
- **Keep an eye on cardinality in your model, not just your measures.** A high-cardinality column dragged
  into a visual (like showing every `TransactionID`) forces VertiPaq to materialize huge intermediate
  results FE then has to process. Aggregate before you visualize.
- **Bidirectional relationships and `DISTINCT COUNT` on high-cardinality columns are two of the most common
  silent performance killers.** Both force VertiPaq to do more cross-table matching per query than a
  single-direction, low-cardinality equivalent. Reach for them deliberately, not by default.
- **Variables (phase 4) aren't just for readability - they're a performance habit.** A value computed once
  into a `VAR` is evaluated once; the same expression repeated three times in a formula (accidentally, from
  copy-pasting) can genuinely be evaluated three separate times.

## Recap

1. **VertiPaq stores data by column, not by row** - a query only pays for the columns it actually touches.
2. **Compression depends on cardinality.** Dictionary encoding and run-length encoding make low-cardinality
   columns nearly free and high-cardinality columns expensive - this is *why* modeling advice about
   cardinality exists, not a rule to take on faith.
3. **Two engines split the work:** the Storage Engine (SE) does fast parallel scans; the Formula Engine
   (FE) does row-by-row DAX logic and is comparatively slow. Fast DAX pushes work to SE; slow DAX forces FE
   to loop.
4. **Performance Analyzer plus DAX Studio show you the split** - Performance Analyzer captures each visual's
   DAX query and total time; DAX Studio's Server Timings tab breaks that into SE time vs FE time - so you can
   stop guessing which part of a formula is the problem.
5. **The habits that follow:** measures over calculated columns for context-aware values, aggregate before
   you iterate, watch cardinality, use bidirectional filters and high-cardinality `DISTINCT COUNT`
   deliberately, and let `VAR` do double duty as both a readability and a performance tool.

You now have the whole arc: what context is (phase 1), how `CALCULATE` bends it (phase 2), the patterns
built from that understanding (phase 3), how to keep it readable (phase 4), and now why some of it is fast
and some of it isn't. That's the real reasoning under DAX - not tricks to copy, a system you can now
predict.

## Quick check

Test yourself on the two ideas that explain almost everything else in this phase - columnar storage plus
cardinality, and the SE/FE split:

```quiz
[
  {
    "q": "Why does VertiPaq store data by column instead of by row?",
    "choices": [
      "So a query only has to read the columns it actually needs, instead of streaming every column of every row past it",
      "Columns compress better than rows regardless of what values are in them",
      "It lets Power BI update individual cells without rewriting the whole table",
      "It's required for DAX row context to work at all"
    ],
    "answer": 0,
    "explain": "Most analytical queries only touch a handful of columns out of many - storing by column means the engine skips every column it wasn't asked for, rather than paying for whole rows it doesn't need."
  },
  {
    "q": "A column of a million unique transaction IDs compresses far worse than a column of a million rows split into 5 country values. Why?",
    "choices": [
      "Dictionary encoding stores a small dictionary plus a compact reference per row - few distinct values means a tiny dictionary, while near-unique values make the dictionary almost as big as the data itself",
      "VertiPaq only compresses numeric columns well, and IDs are usually stored as text",
      "Row count is what determines compression, and both columns have the same row count, so they compress about the same",
      "Unique values compress worse only because they're rarely sorted - sorting the ID column would fix it"
    ],
    "answer": 0,
    "explain": "Cardinality, not row count or data type, is what dictionary encoding cares about - few distinct values means a small dictionary and cheap per-row pointers; near-unique values gain almost nothing."
  },
  {
    "q": "SUMX(Sales, CALCULATE(SUM(Sales[Amount]))) returns the same number as SUM(Sales[Amount]) but is dramatically slower on a large table. What's actually happening?",
    "choices": [
      "SUMX forces the Formula Engine to iterate row by row, triggering a fresh CALCULATE context transition and a separate Storage Engine scan for every row, instead of one single SE scan for the whole column",
      "SUMX always computes a different, more precise result, so the extra time is unavoidable work",
      "The CALCULATE inside SUMX runs entirely inside the Storage Engine, so both formulas should really be equally fast",
      "The slowdown only happens because the measure hasn't been cached yet - it would match SUM after the first run"
    ],
    "answer": 0,
    "explain": "SUM(Sales[Amount]) is one SE scan; wrapping it in SUMX + CALCULATE makes the single-threaded Formula Engine repeat a context transition and a tiny SE scan once per row, which is why identical results can differ by orders of magnitude in runtime."
  }
]
```