4.4 The data model and a first taste of DAX
You can relate two tables and write a measure instead of a hundred lookups.
Before:01. Excel — First StepsUnlocks:06. Excel — Automation
Instead of a hundred lookups copying values between tables, the data model relates tables to each other and measures compute across the relationship. This topic closes the cleaning module with relationships, star-shaped data, and a first handful of DAX: SUM, CALCULATE and DIVIDE. It matters because it is the same modelling idea Power BI is built on, met inside Excel. The common confusion is measures versus calculated columns: a column computes once per row and takes memory, a measure computes at query time inside a pivot, and the measure is nearly always the one you want.
Work through these
Loading to the data model instead of a sheet
Loading tables into the model rather than onto sheets removes the row limit and keeps the workbook small. It is a different destination for the same query.
Relationships and star-shaped data
Relating tables on a key, arranged with facts in the middle and descriptive tables around them, is the shape that makes analysis straightforward. Getting the shape right matters more than any formula.
Measures vs calculated columns
A measure is calculated when it is used and a calculated column is stored with the data, and choosing wrongly costs either performance or correctness. This distinction is the core of the topic.
A first handful of DAX: SUM, CALCULATE, DIVIDE
A handful of formulas in the model's language covers most first analyses, including safe division that does not fail on zero. It is a small vocabulary with a long reach.
Practice file
- xl-05-pivot-sales.xlsx
800 rows of fictional sales data for PivotTables, grouping, % of total and a first measure.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.