Expert Excel · Interactive lesson 4 of 12
Data Model & DAX
A7 related two tables. A real star schema relates several — one fact table full of transactions, surrounded by small dimension tables that describe it — and DAX measures are what make that shape pay off.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialData Modeling and DAX Measures in ExcelSkill check
Try these first:
- Draw a star schema: one fact table, several dimension tables around it.
- Explain why a many-to-many relationship between two fact tables is risky.
- Write a DAX measure using CALCULATE to change filter context.
- Explain why DIVIDE is safer than the
/operator inside a measure.
All familiar? Skip ahead to E5 — VBA Automation.
Concept teach
What you're learning
- Fact & dimension tables
- One fact table (Sales — one row per transaction) surrounded by small dimension tables that describe it (Calendar, Products, Regions) — never the other way around.
- Star schema design
- Every dimension connects directly to the fact table, not to each other — a "star" shape, not a chain. It's the shape that makes filter context behave predictably.
- Relationship direction
- Filters normally flow from the "one" side (a dimension) to the "many" side (the fact table) — a Region filter narrows Sales, not the reverse. A many-to-many relationship breaks that predictability and is worth avoiding.
- Measure tables
- A small, empty table that holds nothing but measures — keeps them out of Sales or Calendar, so anyone opening the model finds every calculation in one obvious place.
- CALCULATE basics
CALCULATEtakes an existing measure and changes the filter context around it — the difference between "Revenue" and "Revenue, but only for East."- Time intelligence concepts
- Functions like
SAMEPERIODLASTYEARshift the current filter's dates back a year, so a prior-period comparison measure works for any month you're viewing, not just one. - DIVIDE for safe ratios
DIVIDE(numerator, denominator)returns blank instead of an error when the denominator is zero — a ratio measure built on/will eventually break a report with a#DIV/0!.- Filter context
- The same measure returns a different number in every cell of a PivotTable because each cell applies its own combination of row, column, and slicer filters — that's filter context, and it's what makes a measure more powerful than a calculated column.
Where to find it in Excel
Power Pivot›Measures›New Measure
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Region | Sales YoY % | |||||||
| 2 | North | 5.8% | |||||||
| 3 | South | -1.2% | |||||||
| 4 | West | 9.4% | |||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 |
Swipe sideways to see the whole window.
- 1Power Pivot › Measures › New Measure.
- 2Keep every measure in one measure table.
- 3DIVIDE returns blank instead of an error when the denominator is 0.
- 4Manage › Diagram View: build the star schema and check relationship direction.
- 5The same measure recalculates for each region: that's filter context.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
A star schema: one Sales fact table, three dimension tables around it. Write the three measures a real model would need.
Independent extension
Build your own star schema
Take a flat export you use and split it into a fact table plus at least two dimension tables (a calendar and one lookup). Relate them, then write Revenue, Margin %, and a prior-period comparison measure — check that the same measure returns different numbers when you slice by different dimensions.
Spot a mistake or have an idea for this lesson? Send feedback