4.2Power Pivot, data model & intro to DAX
The essentials in 30 seconds
- Relate instead of flattening: the data model (Power Pivot) relates several tables via relationships on a common key — a pivot then draws from several tables as if they were one.
- DAX = centralized calculations: a measure (e.g. SUM(Sales[Amount])) lives in the model and recalculates according to the pivot's filters — a single « Revenue » calculation for all the tables.
- Wassim's reflex: as soon as XLOOKUPs multiply, it's time to model — and it's the door to Power BI (same concepts).
Running mini-case — Wassim crosses sales and products without a single XLOOKUP
Wassim, an accountant in Algiers, analyzes revenue by category: his Sales table (ProductCode, Amount) and his Products table (ProductCode, Category) forced him into fragile XLOOKUPs over thousands of rows. He loads both tables into the model, creates a relationship on ProductCode and writes a Total Sales measure. His pivot crosses the category (from Products) and the measure — with no XLOOKUP. Second milestone of the running thread: the Power Query table from 4.1 now feeds a real model.
Continuation of case 4.1 — the table cleaned by Wassim enters the data model.
1. Relate tables instead of flattening everything
Instead of writing XLOOKUPs to gather everything into one giant table, the data model (Power Pivot) relates several tables via relationships (a common key, e.g. ProductCode). A pivot can then draw from several related tables as if they were one. It's the database principle, inside Excel — and Wassim gains lighter, more reliable workbooks from it.
The relationship requires keys of the same type (text with text, number with number) and unique values on the parent side: otherwise the join fails or produces duplicates. You check the types in Power Query, before modeling.
2. The DAX measure
DAX (Data Analysis eXpressions) is the model's calculation language. A measure is a reusable and contextual calculation: Total Sales := SUM(Sales[Amount]) recalculates according to what you filter in the pivot (by region, by month…). A measure lives in the model, not in a cell — all the pivots share the same calculation.
Don't confuse a measure (contextual, recalculated by filter) with a cell formula (fixed, local): it's the contextuality that makes Wassim's model so powerful.
3. Why it's powerful
The model handles large volumes (well beyond a sheet), avoids duplication (no XLOOKUPs everywhere), and centralizes calculations (one « Revenue » measure used by all the pivots). It's the step toward Power BI, which rests on the same concepts (model + DAX).
In practice: you load the cleaned tables (ideally via Power Query) into the model with Add to Data Model, declare the relationships, write the basic measures, then build the pivots.
Did you know?
The pivot is only the shop window; the model is the store.
🔎 Concrete example — 🔎 Concrete example — A pivot over two related tables
Sales table (ProductCode, Amount) related to Products (ProductCode, Category):
Relationship: Sales[ProductCode] → Products[ProductCode]
Measure : Total Sales := SUM(Sales[Amount])
Pivot : Category (from Products) in Rows, [Total Sales] in Values
→ revenue by category WITHOUT any XLOOKUP
// Variant: Algiers revenue only
Revenue Algiers := CALCULATE(SUM(Sales[Amount]), Sales[City] = "Algiers")Reading: the pivot crosses two tables thanks to the relationship, the measure follows the filters.
Lesson: you model (relationships) instead of flattening with XLOOKUPs.
Section vocabulary
- Data model
- A set of tables related by relationships, stored in the workbook.
Simple example: Wassim loads Sales and Products there to cross them in a pivot.
What it is not: a single giant table flattened with XLOOKUPs.
Reflex: several tables to cross durably: load into the model.
- Relationship
- A link between two tables via a common key (e.g. ProductCode), of identical types.
Simple example: Wassim relates Sales and Products on ProductCode.
What it is not: a formula that copies a value row by row.
Reflex: crossing two tables: declare the relationship on the key.
- DAX measure
- A reusable, contextual calculation that adapts to the pivot's filters.
Simple example: Wassim's Total Sales recalculates by month and by city.
What it is not: a fixed, local cell formula.
Reflex: a calculation shared by several pivots: write a measure.
- Filter context
- What is filtered in the pivot and conditions a measure's result.
Simple example: Filtering on Algiers automatically recalculates Wassim's revenue.
What it is not: a fixed value independent of the filters.
Reflex: surprising measure: check the active filters first.
In practice — In practice — Relate two tables
- Load two tables (Sales, Products) into the data model.
- Create a relationship on the common key (ProductCode).
- Create a simple measure: Total Sales := SUM(Sales[Amount]).
- Make a pivot crossing a Products column and the measure, with no XLOOKUP.
Ready-to-say script — explain the model to a colleague (30 s)
« This is Wassim: instead of copying the categories with XLOOKUPs, I related the two tables on the product code. The pivot crosses the tables directly, and my revenue measure recalculates according to the filters. A single calculation for all the tables — and it prepares you for Power BI. »
Say it when someone suggests « yet another XLOOKUP column »: show the multi-table pivot.
Copy-paste template — basic DAX measures
Total Sales := SUM(Sales[Amount])
Sales Count := COUNTROWS(Sales)
Average Basket := DIVIDE([Total Sales], [Sales Count])
Revenue Algiers := CALCULATE(SUM(Sales[Amount]), Sales[City] = "Algiers")Revenue by category
Like Wassim: Sales-Products relationship on ProductCode, pivot with category in rows and the measure in values.
Tracking by city
Filter the pivot on Algiers: the same measure recalculates with no new formula.
Large volumes
Hundreds of thousands of rows: the model absorbs them where the sheet saturates.
Keys of different types
Relationship failure: realign the types in Power Query (text/text), then recreate the relationship.
✓ Best practices
- Model by relationships rather than duplicating.
- Centralize calculations in measures.
- See the model as the door to Power BI.
✗ Common pitfalls
- Flattening everything with XLOOKUPs (heavy, fragile).
- Relationships on keys of different types (fails).
- Confusing a measure (contextual) with a cell formula (fixed).
Key takeaways
- The data model relates several tables by relationships.
- A pivot can draw from several related tables.
- DAX: a measure = a reusable, contextual calculation.
- Same concepts as Power BI (model + DAX).
48h challenge — apply tomorrow
On your real tables: load two of them into the model, create the relationship on the common key, write a SUM measure and cross it in a pivot without XLOOKUP.
- I know how to load tables and create a relationship.
- I know how to write a simple, contextual DAX measure.
- I know how to cross several tables in a pivot without XLOOKUP.
Frequently asked questions
Data model or XLOOKUP?
XLOOKUP to fetch a one-off value; the model to durably analyze several related tables (large volumes, centralized calculations). As soon as XLOOKUPs multiply, it's time to model.
Is DAX hard?
Simple measures (SUM, AVERAGE in the model) are accessible; the power (contexts, CALCULATE) takes practice. You start with basic measures, exactly as you started with =SUM.
What is a relationship in the data model for?