Power Pivot & DAX I

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 a formula per row. If you understood why hardcoding a lookup column is fragile, you already understand why a relationship (which matches on keys, not positions) is sturdier. You also want the aggregation mindset from EX1.01 (SUMIFS/COUNTIFS): 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 that this module then relates and measures.

Feeds forward. EX5.02 (Power Pivot & DAX II) takes the CALCULATE idea into time intelligence (TOTALYTD, SAMEPERIODLASTYEAR, DATESYTD) and context-transition patterns, all of which are CALCULATE wearing different hats, so the override rule you learn here is the load-bearing concept. EX3.02 (Charts & Dashboard Craft) turns these measures into interactive dashboards with slicers, timelines, and cube formulas (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 rather than 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, which is every real reporting job, it wants the model you build here, not another copy of a formula.

Core teaching content

Throughout this module a measure definition is written in the form Name := expression (which is exactly what you type into the Power Pivot Calculation Area), and a reference to that measure is written in square brackets, [Name]. So you define Total Sales := SUM(Sales[Amount]) once, and thereafter you use [Total Sales]. A column is written Table[Column] (Sales[Amount], Region[Name]) so you can always tell a column reference (has a table name) from a measure reference (bare brackets).