Power Pivot & DAX I

Excel Mastery Track · Brown belt (brown-1) · ~7–9 hours · Microsoft 365 on Windows assumed

Every module before this one lived inside the grid. A formula sat in a cell, pointed at other cells, and produced one answer in one place; to get the same answer somewhere else you copied the formula, and to change the logic you hunted down every copy. That model (cells referencing cells) is where most analysts spend their whole careers, and it hits a wall the moment your data outgrows a sheet or your logic needs to be reused a hundred ways. This module is where you climb over that wall. Power Pivot replaces the grid with a data model: your data lives in related tables held in a compressed in-memory engine, and your calculations become measures written in DAX (Data Analysis eXpressions), portable formulas that are evaluated fresh, in context, wherever you drop them. One measure, [Total Sales], gives the right number in a grand total, in a per-region breakdown, in a per-month trend, and inside a slicer-driven dashboard, with no copying and no rewriting. That is the leap from spreadsheet to model, and it is the single biggest jump in analytical power in the entire track.

The mental shift is real and worth naming up front. In a sheet, a number's meaning comes from where it sits, row 12, column D, next to that label. In a model, a number's meaning comes from the filter context it is evaluated in, "sales, filtered to North, filtered to Q1, filtered to this product." You stop thinking "which cell?" and start thinking "which slice?" Once that click happens, the rest of DAX unfolds from a single idea: CALCULATE is the one function that lets you change the slice, and almost everything else is a variation on that theme.

This is the first of the DAX modules, so we build the foundation deliberately and completely. That means what the Data Model is and why it beats sheet formulas; how to arrange tables as a star schema so relationships behave; and the difference between a measure and a calculated column (and why the measure is almost always the right default). Then come filter context and the CALCULATE function that reshapes it; DIVIDE for ratios that never blow up on a zero; how filters cross-filter along relationships; and the elegant trick of a disconnected slicer for what-if scenarios. Everything is written for Microsoft 365, the DAX here matches the DAX in Power BI, so this skill transfers directly the day you open Power BI Desktop.

One orientation note, stated once. Power Pivot and the Data Model ship in Microsoft 365 and in Excel 2016 and later on Windows. If you do not see a Power Pivot tab on the ribbon, enable it with File ▸ Options ▸ Add-ins ▸ Manage: COM Add-ins ▸ Go… ▸ tick "Microsoft Power Pivot for Excel" ▸ OK. Everything below assumes that tab is present.

Learning objectives

By the end you can:

1. Explain what the Data Model is (a compressed, in-memory columnar engine (xVelocity/VertiPaq)) and state three concrete things it does that sheet formulas cannot: hold tens of millions of rows, relate multiple tables without lookups, and compute measures once for reuse everywhere.
2. Load tables into the Data Model three ways (Power Query Load To ▸ Add to Data Model, Power Pivot ▸ Add to Data Model, and Insert ▸ PivotTable ▸ Add this data to the Data Model), and open the model with Power Pivot ▸ Manage.
3. Design a star schema (one central fact table surrounded by dimension tables) and build one-to-many relationships whose "one" side has unique keys, in Diagram View or the Data ▸ Relationships dialog.
4. Distinguish a measure from a calculated column on four axes (when it computes, whether it is stored, whether it sees filter context or row context, and its effect on model size), and default correctly to measures for aggregations and ratios.
5. Write explicit DAX measures in full (SUM, a subtraction measure for gross profit, and DIVIDE) using the Name := expression definition form and the [Name] reference form.
6. Define filter context precisely and use CALCULATE to modify it, writing [North Sales] := CALCULATE([Total Sales], Region[Name] = "North") and predicting what it returns in an already-filtered cell.
7. State the single most important fact about CALCULATE: a boolean filter argument overrides (replaces) any existing filter on that column rather than adding to it, and use KEEPFILTERS when you want intersection instead.
8. Use DIVIDE to guard against divide-by-zero, returning BLANK (or a chosen alternate) instead of #DIV/0!, and explain why that matters for reports and downstream measures.
9. Explain cross-filter direction (single by default, from the "one" side to the "many" side), recognise the risks of bidirectional filters, and distinguish implicit from explicit measures.
10. Build a disconnected slicer (a what-if parameter table) and read the user's selection with SELECTEDVALUE to drive a scenario calculation.

Prerequisites & connections

Builds on. EX0.01 (real Excel Tables, Ctrl+T). Every table you load to the model should be a proper Table with a clean header row and one type per column, because the model inherits that tidiness or chokes on the lack of it. EX1.02 (Lookups Mastery) is the conceptual hinge. A XLOOKUP/INDEX+MATCH lookup is how you pull a fact across tables in the grid, and a relationship is how you do the same thing in the model, declared once for the whole table instead of as a formula per row. If you understood why hardcoding a lookup column is fragile, you already understand why a relationship is sturdier. It matches on keys instead of positions. You'll also want the aggregation mindset from EX1.01 (SUMIFS/COUNTIFS), because a DAX measure is that same "sum across many rows matching criteria" idea, generalised and made reusable. Power Query (EX4.xx) is the usual on-ramp. It shapes and loads the tables, and here you relate and measure them.

Feeds forward. EX5.02 (Power Pivot & DAX II) takes the CALCULATE idea into time intelligence (TOTALYTD, SAMEPERIODLASTYEAR, DATESYTD) and context-transition patterns. Every one of those is CALCULATE wearing a different hat, so the override rule you learn here is the concept the rest stands on. EX3.02 (Charts & Dashboard Craft) turns these measures into interactive dashboards with slicers, timelines, and cube formulas such as CUBEVALUE. On the analyst track, this is the engine room. M2.05's peer-benchmarking pulls comparables as a fact-and-dimension model. M3.08's three-statement model expresses scenarios as measures instead of duplicated tabs. And the KPI packs of M6.xx are, underneath, a handful of well-named DAX measures sliced a dozen ways. The moment a workbook needs the same number, correct, in many slices, it wants the model you build here instead of another copy of a formula. That covers every real reporting job.

Core teaching content

Every measure definition you'll see is written in the form Name := expression, which is what you type into the Power Pivot Calculation Area. To reference that measure, put it in square brackets, [Name]. So you define Total Sales := SUM(Sales[Amount]) once, and from then on you use [Total Sales]. Columns are written Table[Column], as in Sales[Amount] or Region[Name]. That way you can always tell a column reference, which carries a table name, from a measure reference, which is just bare brackets.

DIVIDE and safe ratios

Ratios are the bread and butter of analysis (margin, growth, conversion, yield), and every ratio has the same landmine. Its denominator can be zero or blank. In the grid, =A1/B1 with B1 = 0 returns #DIV/0!, and that error spreads. It poisons any total that includes the cell, breaks conditional formatting, and looks broken in a report. DAX gives you a purpose-built guard for it, called DIVIDE.

DIVIDE( <numerator>, <denominator>, [<alternateresult>] )

Normally DIVIDE returns numerator / denominator. When the denominator is 0 or BLANK, it returns the optional alternateresult. Leave out that third argument and you get BLANK instead of an error. So the gross-margin measure of Worked example 2 is written:

Gross Margin % := DIVIDE( [Gross Profit], [Revenue] )

Take a product line with zero revenue this period. [Revenue] is 0, so DIVIDE returns BLANK, and a PivotTable shows an empty cell where #DIV/0! would have been. That's the right answer. A product with no sales has no margin, because the ratio is undefined, and a blank says so without breaking the report. Compare the naïve [Gross Profit] / [Revenue], which errors on that row and drags the error into every subtotal above it.

Two refinements are worth knowing now. First, if you want a specific fallback instead of blank, supply it. Say you want 0 so a downstream sum keeps working. Then you write DIVIDE( [Gross Profit], [Revenue], 0 ), and you make it a conscious choice, the same way you did with XLOOKUP's if_not_found. Second, DIVIDE is faster and clearer than the equivalent IF( [Revenue] = 0, BLANK(), [Gross Profit] / [Revenue] ), and its error handling is prettier too. That IF is the verbose thing people write before they learn DIVIDE exists. Any time a denominator could conceivably be zero, use DIVIDE. In real data it always eventually is. Common mistake #4 is using plain / and shipping a report that erupts in #DIV/0! the first month a product has no sales.

Disconnected slicers

Every slicer so far has filtered the fact through a relationship. There's a beautiful, slightly mind-bending alternative, a slicer on a table that's related to nothing. It's called a disconnected slicer, or a what-if parameter table. Because it's disconnected, selecting a value cannot filter the fact, since there's no pipe for the filter to travel down. Instead, a measure reads your selection and uses it as an input to a calculation. That's how you build interactive scenario analysis entirely in the model: a growth-rate dial, a price-uplift lever, an FX-rate assumption.

The pattern has three parts:

1. A one-column parameter table, disconnected from everything. For a price-uplift scenario you might load a tiny table Uplift with one column Pct holding 0, 0.05, 0.10 (0%, 5%, 10%). It relates to no other table.
2. A slicer on that column, added with Insert ▸ Slicer on Uplift[Pct], which gives the user buttons to pick a scenario.
3. A measure that reads the selection with SELECTEDVALUE and applies it. SELECTEDVALUE( Uplift[Pct], 0 ) returns the single selected value, or 0 if nothing is selected or more than one thing is. So:

Adjusted Sales := [Total Sales] * ( 1 + SELECTEDVALUE( Uplift[Pct], 0 ) )

Pick 10% on the slicer and [Adjusted Sales] recomputes as total sales times 1.10, across every slice of the report simultaneously. It works because the measure re-evaluates in each cell's context while reading the same disconnected selection. Nothing was filtered through a relationship. All the slicer did was feed a number into a measure. It's the DAX-native cousin of the classic Data ▸ What-If Analysis data tables you met earlier, and it's more flexible, because the scenario lever now drives any measure in the entire model at once.

SELECTEDVALUE is the clean modern reader. The older idiom IF(HASONEVALUE(Uplift[Pct]), VALUES(Uplift[Pct]), 0) does the same job, and you'll want to recognise it in inherited models. Either way, the disconnected-slicer pattern is your first taste of measures as interactive functions of user input. That idea is the foundation of the dashboards in Charts & Dashboard Craft.

Common mistakes & how experts think differently

1. Reaching for a calculated column when a measure is right. It's the signature error in Power Pivot. The beginner, fresh from the grid, adds Sales[Line Margin] = Sales[Revenue] - Sales[COGS] as a stored column and sums it in a PivotTable, because a column feels like a cell formula. It appears to work, but it's wrong in two ways. First, it bloats the model with a materialised column. That can mean millions of stored values. You also get a slower refresh and a bigger file. Second, it freezes a row-level calculation that can't flex with context the way a measure would. A summed margin column and a properly re-aggregated margin measure diverge the instant a ratio is involved. The expert's reflex runs the other way. Aggregations and ratios always become measures, and a calculated column is only for a per-row attribute to slice or group by, such as a category label or a fiscal-period flag. Here's a quick test. If you'll drag it onto Rows/Columns/Slicers, it's a column. If it goes into Values, it's a measure. When unsure, write the measure.

1. Many-to-many and ambiguous relationships. Try to relate two tables that both have duplicate keys, say two tables listing "region" with repeats, joined directly. You get a many-to-many that either won't build or behaves unpredictably. Wiring two filter paths between the same pair of tables causes trouble too. It forces one path inactive, and then a measure ignores a slicer and surprises you. The expert never joins fact-to-fact and never relies on non-unique keys. They insert a proper dimension table with unique keys so every join is a clean one-to-many. And they keep a single filter path between any two tables. All of that is just the star schema, followed properly.

1. Forgetting that CALCULATE overrides filter context instead of adding to it. It's the deepest conceptual trap you'll meet here. A cell filtered to South, holding CALCULATE([Total Sales], Region[Name]="North"), returns North's number. It doesn't go blank, because the boolean filter replaces the column's existing filter. It expands to FILTER(ALL(Region[Name]),…), and the ALL wipes the prior filter first. Beginners predict blank, picturing South ∩ North. Experts predict North because of the override, and they reach for KEEPFILTERS on the rare occasions they actually want the intersection. Say it aloud until it's reflex: a boolean filter in CALCULATE overwrites that column unless wrapped in KEEPFILTERS.

1. Using / instead of DIVIDE. Plain division erupts in #DIV/0! the first period a denominator is zero, whether that's a new product with no sales or a region that opened mid-year. From there the error contaminates every subtotal it touches. The expert writes DIVIDE([Gross Profit],[Revenue]) for every ratio, so a zero denominator yields a clean BLANK, or a deliberate alternate like 0. Plain / is kept for the rare case where a zero denominator is impossible, and even then the expert usually doesn't bother with it. Treat DIVIDE as the default for ratios, never as optional polish.

1. Switching relationships to bidirectional by reflex. A single-direction filter occasionally "doesn't reach" where a beginner expects, so they flip the relationship to Both to force it. They inherit ambiguous filter paths, inflated totals, and slower queries, often without noticing that the totals are now wrong in ways that are hard to spot. The expert leaves relationships single-directional by default, from dimension to fact. Bidirectional is a scalpel, used on one specific relationship with a stated reason, and after any such change the expert checks the grand total still reconciles. Single direction is a guardrail that keeps the model's numbers trustworthy, so don't treat it as a limitation to route around.

1. Shipping implicit measures instead of explicit ones. Drag a numeric field into Values and let Excel auto-create "Sum of Amount", and you've made an implicit measure. It's convenient, but it's tied to that one pivot, un-reusable, and invisible to other DAX. The expert writes explicit named measures, such as Total Sales := SUM(Sales[Amount]). That way the logic is defined once, reused everywhere, referenceable by other measures, and portable to Power BI. Naming the measure also names the concept, and that's half of a legible model.

1. A fact table with a muddled grain. Say the fact mixes row meanings, with some rows per order, some per line item, plus a stray subtotal row. Every measure then goes wrong without telling you, and no CALCULATE can fix arithmetic built on an incoherent base. The expert fixes the grain first ("one row = one line item, full stop"), pushes descriptive attributes out to dimensions, and only then writes measures. Clean grain in, trustworthy measures out.

Underneath all seven is one habit. A professional thinks in tables, relationships, and context instead of cells. Beginners carry grid habits (columns everywhere, plain division, "make the filter reach") straight into the model, and they're puzzled when it fights back. Experts let the model's own logic do the work: the star schema, measures by default, and CALCULATE as the one context-changer.

Worked examples

Worked example 1: India regional sales: a star schema with SUM and CALCULATE (India, ₹)

The task. An Indian electronics distributor wants a reporting model over one quarter of sales. Your job is to build a star schema, write [Total Sales] and [North Sales], and check both against the raw data. All amounts are in rupees (₹).

Step 1, the tables (fix the grain first). Our fact is Sales, at the grain one row per sale line. It carries foreign keys and the numeric Amount we'll sum. Three dimensions describe what we slice by: Region, Product, and Date.

Here's the Region dimension, with one row per region and a unique RegionID:

     RegionID    Name
     1           North
     2           South
     3           East
     4           West

And here's the Sales fact, one row per sale line, in ₹:

     SaleID   ProductID   RegionID   Amount     Quantity
     1        P01         1          120000     40
     2        P02         2          85000      50
     3        P01         3          60000      20
     4        P03         4          45000      30
     5        P02         1          95000      55
     6        P03         2          70000      42
     7        P01         1          55000      18
     8        P02         3          40000      25
     9        P03         4          90000      60
     10       P01         1          75000      25

Product, with P01–P03, and Date, at month grain, complete the star. You don't need them for the two measures below, but they make the model sliceable by product and month.

Step 2, the relationships. In Diagram View, drag Sales[RegionID] onto Region[RegionID], Sales[ProductID] onto Product[ProductID], and Sales[DateKey] onto Date[DateKey]. Each line shows a 1 on the dimension and a ∗ on Sales, so you get three clean one-to-many spokes of the star. Keys on the "one" side are unique, with each RegionID appearing once in Region. On the "many" side, Sales repeats them freely.

Step 3, the base measure. In the Sales Calculation Area, define:

Total Sales := SUM( Sales[Amount] )

That's an explicit measure, with no stored column behind it. It computes at query time in whatever context the cell holds.

Step 4, the CALCULATE measure. Force the filter context to North:

North Sales := CALCULATE( [Total Sales], Region[Name] = "North" )

Read it as "compute [Total Sales], but with the filter context overridden to Region[Name] = "North"." Because the boolean argument replaces any region filter, [North Sales] returns North's total whichever region's row it's in.

Step 5, compute and verify (every figure recomputed in Python). [Total Sales] sums all ten Amount values. [North Sales] sums only the rows whose RegionID maps to North, and RegionID 1 maps to SaleIDs 1, 5, 7, 10:

Dropping [Total Sales] into a matrix on Region[Name] gives the full breakdown, and the parts reconcile to the whole:

Now check it. Adding the four regions gives 3,45,000 + 1,55,000 + 1,00,000 + 1,35,000 = 7,35,000, which matches [Total Sales]. And the North row, 3,45,000, matches [North Sales] too. North's share is 3,45,000 / 7,35,000 = 46.94%.

Step 6, see the override in action. Put [Total Sales] and [North Sales] side by side in the region matrix:

[North Sales] reads 3,45,000 on every row (including South, East, West) because CALCULATE overrode each row's region filter to North. It looks wrong the first time you see it. It's the override rule doing its job, though, and it's what makes [North Sales] usable as a fixed benchmark, "each region vs North". If we'd wanted it to respect the row and show North only on the North row, with blanks elsewhere, we'd have wrapped the filter in KEEPFILTERS.

Step back and look at what you built. One base measure plus one CALCULATE gave you both a live total and a fixed regional benchmark without a single copied formula. Both are provably correct against the raw rows.

Worked example 2: US product margins: DIVIDE and the divide-by-zero guard (US, $)

The task. A US networking-hardware distributor wants gross margin by product and company-wide. Write [Revenue], [Gross Profit], and a DIVIDE-guarded [Gross Margin %]. Then prove the margin comes out blank for a product with zero revenue this period, where plain division would show #DIV/0!. Amounts are in dollars ($).

Step 1, the fact table. It's Sales, with one row per product line this period, in $. Gross Profit is Revenue − COGS:

     SKU        Product          Revenue    COGS
     ALP-100    Alpha Router     250000     150000
     BRV-200    Bravo Switch     180000     126000
     CHR-300    Charlie Modem    0          0
     DLT-400    Delta Firewall   320000     224000

Charlie Modem is a newly listed or paused SKU that carried no sales this period, so its revenue is 0 and its COGS is 0. It's your divide-by-zero test case.

Step 2, the base measures. Write them as explicit measures in the Calculation Area:

Revenue      := SUM( Sales[Revenue] )
Gross Profit := SUM( Sales[Revenue] ) - SUM( Sales[COGS] )

Notice that [Gross Profit] is a measure, the difference of two sums. We didn't write a calculated column Sales[GP] = Sales[Revenue]-Sales[COGS] and then sum it. As a measure it stores nothing and stays correct in every slice, which keeps you clear of common mistake #1.

Step 3, the guarded ratio.

Gross Margin % := DIVIDE( [Gross Profit], [Revenue] )

On Charlie's row [Revenue] is 0, so DIVIDE returns BLANK, an empty cell, instead of #DIV/0!.

Step 4, compute and verify (every figure recomputed in Python). For each product, margin = Gross Profit ÷ Revenue, computed with DIVIDE:

Step 5, the guard, made explicit. On Charlie's row the naïve measure [Gross Profit] / [Revenue] evaluates 0 / 0, which returns #DIV/0!. That error would spread into the company total and break the whole margin column. DIVIDE( [Gross Profit], [Revenue] ) returns BLANK instead, which the PivotTable shows as an empty cell. It's also the arithmetically correct answer, because a product with no sales has no margin. If you'd prefer a literal 0.00% there for cosmetic reasons, write DIVIDE( [Gross Profit], [Revenue], 0 ) as a deliberate choice.

Now look at the company margin, 33.33%. It's [Gross Profit] ÷ [Revenue] re-evaluated at the company grain, and you shouldn't read it as the simple average of the product margins. A measure re-aggregates in each context. It doesn't average the rows beneath it. And because DIVIDE guarded the denominator, the zero-revenue SKU just shows a blank instead of detonating the report.

Practice set

Work each problem before you read its solution. Assume every source is a proper Table loaded to the Data Model, and that measures are explicit unless stated otherwise. Where a problem asks for a measure, give it in the Name := expression form.

P1 (guided). Data Model vs sheet. In one or two sentences each, name two things the Data Model does that a worksheet can't, and name the ribbon path that opens the model for editing.

Solution. (1) It holds far more than the sheet's 1,048,576-row limit, tens of millions of rows, because VertiPaq stores data as compressed columns instead of cells. (2) It relates tables natively through declared relationships, so a fact row "knows" its dimension attributes without a per-row lookup formula. If you're asked for a third, it's that measures compute once and are reused everywhere in context. The model opens with Power Pivot ▸ Manage.

P2 (guided). Fact or dimension? Classify each as a fact or a dimension table, and state the grain of the fact. (a) A table with one row per sales line, carrying ProductID, RegionID, DateKey, Amount, Quantity. (b) A table with one row per product, carrying ProductName, Category, Brand.

Solution. (a) is the fact table, and its grain is one row per sales line: one product sold, one region, one date. (b) is a dimension table, Product, with one row per distinct product and descriptive attributes you slice by. Facts hold the numbers you sum. Dimensions hold the labels you group by.

P3 (guided). The base measure. Write the explicit DAX measure [Total Sales] that sums the Amount column of the Sales fact, and explain in one line why this is a measure instead of a calculated column.

Solution. Total Sales := SUM( Sales[Amount] ). It's a measure because it's an aggregation that must respond to context. The same definition has to give the grand total, each region's subtotal, and each month's value, depending on the slice. A calculated column would store a per-row value and couldn't re-aggregate to the current filter context.

P4. CALCULATE and the override rule. Use the model from the first worked example, with Sales[Amount] and a Region dimension whose Name is one of {North, South, East, West}. Write [North Sales]. Then predict what [North Sales] shows in the South row of a matrix broken out by Region[Name], and why.

Solution. North Sales := CALCULATE( [Total Sales], Region[Name] = "North" ). In the South row it shows North's total, ₹3,45,000. It doesn't go blank. The boolean filter Region[Name]="North" overrides the row's Region = South filter. It expands to FILTER(ALL(Region[Name]), Region[Name]="North"), whose ALL clears the row filter first, so the cell computes North whatever row it's in. To make it respect the row, showing North only on the North row and blank elsewhere, you'd wrap the filter in KEEPFILTERS.

P5 (numeric). Total and North sales. Use the Sales fact from the first worked example. By SaleID, the amounts are 120000 for 1, 85000 for 2, 60000 for 3, 45000 for 4, 95000 for 5, 70000 for 6, 55000 for 7, 40000 for 8, 90000 for 9, 75000 for 10. North is SaleIDs 1, 5, 7, 10. Compute [Total Sales] and [North Sales] in ₹.

Solution. [Total Sales] = 120000+85000+60000+45000+95000+70000+55000+40000+90000+75000 = ₹7,35,000. [North Sales] = 120000+95000+55000+75000 = ₹3,45,000. To cross-check, add the four region subtotals. North 3,45,000, South 1,55,000, East 1,00,000 and West 1,35,000 sum to 7,35,000.

P6. Gross Profit as a measure. A Sales fact has Revenue and COGS columns. Write [Gross Profit] as a measure, and say in one line why writing it as a calculated column Sales[GP] = Sales[Revenue] - Sales[COGS] and summing that is the inferior choice.

Solution. Gross Profit := SUM( Sales[Revenue] ) - SUM( Sales[COGS] ). The calculated-column route is inferior because it materialises a stored column on every fact row. That bloats the model and slows refresh, and you get nothing for it. As a measure, it computes the identical total at query time with zero storage and stays correct in every slice. Save calculated columns for per-row attributes you must slice by. Values you'll sum belong in measures.

P7 (numeric). Guarded margin. Take [Gross Profit] := SUM(Sales[Revenue]) - SUM(Sales[COGS]) and [Revenue] := SUM(Sales[Revenue]), and the rows Alpha (Rev 250000, COGS 150000), Bravo (180000, 126000), Charlie (0, 0), Delta (320000, 224000). Write [Gross Margin %] with a divide-by-zero guard and compute the company-wide margin as a percentage. What does the measure return for Charlie?

Solution. Gross Margin % := DIVIDE( [Gross Profit], [Revenue] ). Company [Gross Profit] = (250000+180000+0+320000) − (150000+126000+0+224000) = 750000 − 500000 = 250000. Company [Revenue] = 750000. So the margin = 250000 / 750000 = 33.33%. For Charlie, [Revenue] = 0, so DIVIDE(0, 0) returns BLANK, an empty cell, where plain division would give #DIV/0!. That's the right result for a product with no sales.

P8. DIVIDE vs slash. Rewrite = [Gross Profit] / [Revenue] safely, and state what each version returns when [Revenue] = 0. When would you pass a third argument to DIVIDE?

Solution. Written safely, it's DIVIDE( [Gross Profit], [Revenue] ). With [Revenue] = 0, the slash version returns #DIV/0!, an error that propagates into every total it touches. DIVIDE returns BLANK by default. Pass a third argument, as in DIVIDE([Gross Profit], [Revenue], 0), when you deliberately want a specific fallback like 0 instead of blank. Usually that's so a downstream sum or chart keeps working. Make it a conscious decision instead of a habit.

P9. Cross-filter direction. By default, does a one-to-many relationship cross-filter from dimension to fact or from fact to dimension? Give one reason the default is single direction and one risk of switching a relationship to bidirectional.

Solution. By default it filters from the "one" side, the dimension, to the "many" side, the fact. That's what you want when a slicer on a dimension should filter the fact's measures. The single default keeps filter paths short, predictable, and fast. Switching to bidirectional risks ambiguous filter paths, with two routes between tables and unpredictable totals, and it degrades performance. Use it only on a specific relationship with a clear reason, and check that the grand total still reconciles.

P10. Implicit vs explicit. Distinguish an implicit measure from an explicit one, and give one concrete reason to always create explicit measures.

Solution. An implicit measure is auto-created when you drag a numeric field into a PivotTable's Values area, where Excel adds "Sum of Amount". It's tied to that one pivot and can't be reused or referenced by other DAX. An explicit measure is a named DAX formula you define once, such as Total Sales := SUM(Sales[Amount]). Prefer explicit because it's defined once and reused everywhere. Other measures can reference it, the way [North Sales] calls [Total Sales]. It's portable to Power BI, and it names the concept, which keeps the model legible.

P11 (timed: 6 min). Disconnected slicer. You want a price-uplift what-if with buttons for 0%, 5%, 10% driving a [Adjusted Sales] measure off [Total Sales]. Describe the three parts of the pattern and write the measure. Why must the parameter table be disconnected?

Solution. (1) A one-column parameter table Uplift with Pct ∈ {0, 0.05, 0.10}, related to nothing. (2) A slicer on Uplift[Pct]. (3) A measure reading the selection: Adjusted Sales := [Total Sales] * ( 1 + SELECTEDVALUE( Uplift[Pct], 0 ) ). It has to be disconnected because we don't want the selection to filter the fact through a relationship. Instead, the measure reads the chosen number and applies it, so the same lever adjusts every slice of the report at once. Selecting 10% makes [Adjusted Sales] = [Total Sales] × 1.10 everywhere.

P12 (timed: 6 min). Choose the right construct. Name the best construct for each one below and justify it in one line. (a) A value you'll drop onto a slicer to group sales by. (b) A company-wide gross-margin % that must react to every slicer. (c) Forcing a measure to compute for the North region regardless of the current row. (d) A ratio whose denominator can be zero. (e) A what-if growth-rate lever that drives a measure.

Solution. (a) A calculated column, or a dimension attribute. You slice by it, so it has to exist as a column. (b) An explicit measure with DIVIDE, because it's an aggregation or ratio that must respond to context. (c) CALCULATE with a boolean filter, the one function that overrides filter context. (d) DIVIDE, which guards the zero denominator and returns BLANK instead of #DIV/0!. (e) A disconnected slicer read by SELECTEDVALUE inside a measure. Before you type, get in the habit of asking whether you'll slice by it or aggregate it, and whether its context is fixed or reactive. Then ask whether the denominator could be zero.

Applied mini-project

Your project is a lookup-free sales-and-margin model. Assemble a small but complete Power Pivot model that reports sales and margin many ways from one set of measures, with no VLOOKUP, no copied formulas and no numbers typed twice.

Set up the tables first. Make each one a real Table (Ctrl+T) and load it with Power Pivot ▸ Add to Data Model:

1. Sales is the fact, at a grain of one row per sale line, with DateKey, ProductID, RegionID, Revenue, COGS, Quantity. Give it at least 20 rows, mixing an Indian set (₹) and a US set ($). Or run two copies of the model, one per currency, so you exercise both [Total Sales] in ₹ and [Gross Margin %] in $. Include at least one product-period with zero revenue to test DIVIDE.
2. Product is a dimension: ProductID (unique), ProductName, Category.
3. Region is a dimension: RegionID (unique), Name (North/South/East/West), Zone.
4. Date is a dimension: DateKey (unique), Month, Quarter, Year.
5. Uplift is the disconnected parameter, one column Pct ∈ {0, 0.05, 0.10}, related to nothing.

Then build the model and measures:

1. In Diagram View, create one-to-many relationships Sales[ProductID]→Product, Sales[RegionID]→Region, Sales[DateKey]→Date. Confirm the 1 is on each dimension, and leave every relationship single-direction. Leave Uplift disconnected.
2. Write explicit measures: Total Sales := SUM(Sales[Revenue]), Total COGS := SUM(Sales[COGS]), Gross Profit := [Total Sales] - [Total COGS] and Gross Margin % := DIVIDE([Gross Profit],[Total Sales]).
3. Write North Sales := CALCULATE([Total Sales], Region[Name]="North"), and a second version North Sales (kept) := CALCULATE([Total Sales], KEEPFILTERS(Region[Name]="North")). Put both in a matrix on Region[Name] and explain the difference you observe. You should see the plain one show North on every row, while the KEEPFILTERS one shows North only on the North row, and blank elsewhere.
4. Write Adjusted Sales := [Total Sales] * (1 + SELECTEDVALUE(Uplift[Pct], 0)), add a slicer on Uplift[Pct], and confirm picking 10% scales [Adjusted Sales] to 1.10× across every slice at once.
5. Build a PivotTable that proves reuse: Region[Name] on rows, Product[Category] on a slicer, Date[Quarter] on columns, and [Total Sales], [Gross Margin %], [Adjusted Sales] in Values. Slice it several ways and confirm the numbers reconcile.
6. Do a robustness pass. Find the zero-revenue product's margin cell and confirm it shows a blank instead of #DIV/0!. Then check that the region subtotals sum to the grand total in every slice. They must, if your grain is clean and your relationships are single-direction.

Rubric. Score yourself on each criterion, where 0 = missing/wrong, 1 = partial and 2 = correct and defended:

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its star schema, measure set, and disconnected-slicer pattern are the skeleton you'll reuse and extend once you reach time intelligence.

Lab: open the Power Pivot & DAX workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Function and concept references (reach for these first):

• Microsoft Learn, the official DAX function reference and the pages for CALCULATE, DIVIDE, SUM, and SELECTEDVALUE. Search "CALCULATE DAX Microsoft Learn" to find them. They give authoritative argument definitions and the precise semantics of filter modification. [Free] [Intermediate]
• Microsoft Learn. "Create a Data Model in Excel" and "Create a relationship between tables" (support.microsoft.com) are the canonical UI walkthroughs for loading tables and drawing relationships. [Free] [Beginner]

Deeper DAX (the definitive treatments):

• SQLBI (sqlbi.com): Marco Russo & Alberto Ferrari's articles, especially "Filter context" and "Understanding CALCULATE". They're the clearest first-principles explanation of the one idea everything here is built on. Their book The Definitive Guide to DAX is the reference. [Free articles / Paid book] [Intermediate → Advanced]
• DAX Guide (dax.guide): a SQLBI companion with every DAX function, its signature, parameters, and examples, updated to the current engine. Bookmark it. [Free] [All levels]

Pattern libraries and moving pictures:

• daxpatterns.com: ready-made patterns (parameter tables / what-if, cumulative totals, ratios) with downloadable files. You'll find the disconnected-slicer pattern there in full. [Free] [Intermediate]
• Any reputable "Star schema for Power BI/Power Pivot" explainer. Get why the star beats the snowflake firmly into your head before you build your third model. [Free] [Beginner]

Bridge forward: the time-intelligence functions of EX5.02 (TOTALYTD, SAMEPERIODLASTYEAR) are all CALCULATE with a date filter under the hood. Re-read the CALCULATE section above once before you start, because the whole of that next step leans on the override rule.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand. That's how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds to the hand method and never replaces it. (R1/R10)

AI-Augment this skill

{
  "skill": "Filter context versus row context, and CALCULATE's context transition — the mechanism by which a row-context expression wrapped in CALCULATE silently converts into a filter on that row's own columns",
  "use": "A DAX measure that returns a suspiciously round number — every row showing exactly 100%, every share showing exactly 1 — is almost always a context-transition bug, and it is one of the hardest DAX failures to spot by staring at the formula, because the formula LOOKS correct. Use an assistant to explain what filter context a specific CALCULATE call is actually running in at a named cell, and to draft the ALL()-wrapped correction — but predict the wrong-vs-right numbers yourself first, so you can tell whether the assistant's explanation matches what the model actually does rather than a plausible-sounding guess.",
  "tools": ["Coding assistants — Claude, ChatGPT, or a notebook's inline assistant to draft the pandas row-share/groupby-share contrast below", "Chat assistants — for walking through what filter context a specific cell in a matrix carries before you write the measure", "Spreadsheet-reading AI — for auditing an inherited workbook's measures for a missing ALL() before you trust its totals"],
  "prompt": "Here is a DAX measure computed inside a calculated column or an iterator: <PASTE>. Explain what filter context CALCULATE is running in at THIS row, whether a context transition is occurring, and what the measure will actually return at three or four specific rows. Do not just tell me the measure 'looks right' — show me the row-by-row numbers so I can check them against what I expect by hand.",
  "verify": "Predict the measure's output for at least two specific rows by hand — one where you expect the row-context trap to fire and one where ALL() should fix it — before running it, and treat any mismatch between your prediction and the assistant's explanation as a signal to re-derive the filter context yourself rather than defer to the explanation.",
  "diy": "The gate is unaided: given a small fact table, you write CALCULATE with a boolean filter, predict by hand what it returns in a row whose own filter the boolean overrides, and state the override rule from memory — with no tool in the room."
}

Modern Data Analysis

By hand first. You already write [Total Sales], wrap it in CALCULATE with a boolean filter, and know the override rule. That rule says a boolean filter inside CALCULATE replaces the column's existing filter instead of adding to it. Code gives you a fast, disposable way to predict a measure's output before you build it in Power Pivot. You catch a context-transition bug on paper, or in a notebook cell, instead of discovering it live in a matrix full of confident, identical 100% figures.

Today's workflow. Filter context in DAX has a direct pandas analogue. df.groupby('Region')['Amount'].transform('sum') broadcasts each group's total back onto every row of that group, which is the row-context equivalent of a correctly ALL()-scoped CALCULATE. Now write both versions side by side. The wrong DAX pattern, Amount / CALCULATE(SUM(Amount)) with no ALL(), has no natural pandas equivalent at all. In pandas, transform always operates over the group. It never collapses to the single current row without warning, the way an un-ALL()-ed CALCULATE inside row context does. That in itself is a clue that the un-scoped DAX pattern is the anomaly.

Tools & sources (IN + US). pandas for the groupby/transform contrast below. For India, the same ten-row regional Sales fact you built in the first worked example. For the US, the four-product Gross Margin fact from the second worked example.

# recomputed in-session — R3
sales = [120000,85000,60000,45000,95000,70000,55000,40000,90000,75000]
total = sum(sales)
wrong = [amt/amt for amt in sales]          # CALCULATE(SUM(Amount)) with no ALL(): denominator = the row itself
correct = [amt/total for amt in sales]      # CALCULATE(SUM(Amount), ALL(Sales)): denominator = the whole table
print(sum(wrong), min(correct), max(correct), sum(correct))

Verify. [Total Sales] recomputes to ₹7,35,000 and [North Sales] to ₹3,45,000, both matching the first worked example's figures. North's share recomputes to 3,45,000 ÷ 7,35,000 = 46.94%, which matches the worked figure to the decimal.

Quantitative lens

CALCULATE's context transition is where most wrong numbers come from, and it leaves a specific, recognisable failure signature. Write a calculated column or iterator expression Amount / CALCULATE(SUM(Amount)) with no ALL(), intending a per-row share of the total. Inside row context, CALCULATE triggers a context transition. It converts the current row into an equivalent filter on that row's own columns before evaluating. So SUM(Amount) inside the CALCULATE is filtered down to this row alone, and the expression becomes Amount / Amount for every row. On the ten-row Sales fact from the first worked example, every single row reports 100.00%, and the ten rows "sum" to a nonsensical 1000%.

Now write the correct version. Wrap the same CALCULATE in ALL(Sales) to clear that row-level filter explicitly before summing, and the ten rows report their true shares. They range from 5.44% (SaleID 8, ₹40,000) to 16.33% (SaleID 1, ₹1,20,000) and sum to 100.00%. The failure signature is easy to spot because it's so uniform. Picture a column of identical round numbers, 100% or, equivalently, a ratio of 1, across rows that plainly hold different values. That's the sign a context transition fired where the author expected the row's own filter to survive.

One level up, the same trap breaks a region-level share too, and it gives no warning. Extend the region matrix from the first worked example and use CALCULATE([Total Sales], ALL(Region)) as the denominator. Done correctly, the region-level shares are North 46.94%, South 21.09%, East 13.61%, West 18.37%. They sum to 100.00%, and North's figure matches the share stated in the worked example. Omit the ALL(Region) in a calculated-column version of the same idea and every region row collapses again, reporting its own value over itself. You get 100% everywhere, for the identical structural reason as the row-level case. That's because a calculated column always carries row context, and any un-scoped CALCULATE inside it triggers the same transition.

Limits: the "wrong" pattern above is deliberately written as a calculated column or iterator expression, where row context is present. Write the same un-ALL()-ed CALCULATE as an ordinary measure, evaluated in a matrix's filter context with no row context, and it doesn't trigger a context transition. It behaves differently again. So the failure quantified here is specific to row-context evaluation. It doesn't apply to every CALCULATE call that omits ALL().

Where this goes next: galaxy cross-links

• G2 · CS2.03 Databases & SQL for Analysts. Filter context has a direct analogue in a SQL WHERE/GROUP BY scope, and CALCULATE's override behaviour is the DAX equivalent of a query that replaces a predicate instead of adding one.
• G2 · DA1.02 pandas I: Data Wrangling: groupby().transform() is the pandas-native way to broadcast a group aggregate back onto every row, the correct-pattern analogue of an ALL()-scoped CALCULATE.
• G1 · QM1.02 Hypothesis Testing, Correlation & Regression. You have to state which population a ratio's denominator is drawn from, and the context-transition trap above breaks that rule when the denominator narrows to one row without warning.

Teach it back & journal

Feynman prompt. Picture a shopkeeper who keeps separate notebooks: one listing every sale, one listing every product, one listing every area of the city. Explain to them how a "data model" is just declaring, once, that the sale's product-code matches the product notebook and its area-code matches the area notebook. From then on, any sale automatically "knows" its product's name and its area's manager without copying anything. Then explain the one twist that trips everyone. When the shopkeeper asks "how much did the North area sell?", the answer depends on which slice the question fixes and has nothing to do with where the question is written. Explain, too, that a special instruction, CALCULATE, lets you fix the slice to North even while you're standing in the South column. Only in your last two sentences may you use the words fact table, dimension, filter context, and CALCULATE. Attach each one to the moment in the story it describes.

Journal prompt. Find one workbook you already maintain that stitches tables together with lookups, such as a sales tracker that VLOOKUPs product categories or a report that copies a SUMIFS into six tabs. Sketch, on paper if you like, how it would become a star schema. Decide which single table is the fact, holding the events, and which tables are dimensions, the things you slice by. What one measure would replace the copied formulas? Then write two sentences you want to remember forever. In the first, say why a measure computed once in filter context beats a formula copied into many cells. In the second, say why CALCULATE's override behaviour, replacing a filter instead of adding to it, is the rule you'll most often get wrong until it becomes reflex. Add how you'll check yourself, by predicting the South-row answer before you look.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.