Power Pivot and the Data Model: relationships and DAX

Link several tables with relationships instead of VLOOKUP columns, build a star schema and understand the basic DAX measures (SUM, CALCULATE, DIVIDE) together with filter context.

  • University
  • 24 min
  • 10 questions
  • Lesson 26 of 27

In this lesson you will learn

  • Separate fact and dimension tables and create a one-to-many relationship
  • Explain the difference between a calculated column and a measure
  • Write simple measures with SUM, CALCULATE and DIVIDE
  • Understand how filter context changes a measure's result

A company's sales table has millions of rows, while customers and products live in separate tables. The usual approach: add “City”, “Category” and “Price” columns to the sales table with VLOOKUP. The result is a huge, slow file that also hits the worksheet limit of 1,048,576 rows. The Data Model keeps the tables separate, links them with relationships and works on compressed data in memory; Power Pivot is the tool for managing that model.

The star schema and relationships

Fact and dimension tablesDefinition

A fact table stores events (each sale is one row: date, customer ID, product ID, amount). Dimension tables describe the event (customers, products, calendar), and each key appears there only once. The dimensions sit around the central fact table like a star.

  1. Prepare the tables

    Turn each list into an Excel Table with Ctrl+T and name it: Sales, Customers, Products (Table Design › Table Name).

  2. Add them to the model

    In the Insert › PivotTable dialog tick Add this data to the Data Model, or enable Power Pivot: File › Options › Add-ins › Manage: COM Add-ins › Go… › Microsoft Power Pivot for Excel; then Power Pivot › Tables › Add to Data Model.

  3. Create the relationship

    Data › Data Tools › Relationships › New…: Table = Sales, Column (Foreign) = CustID; Related Table = Customers, Related Column (Primary) = CustID. In the Power Pivot window (Diagram View) you can also drag a field from one table onto the other.

Interactive

Loading simulation…
A tiny star model: sales (in manat) and customers; the relationship's logic shown with VLOOKUP.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

DAX: calculated columns and measures

AspectCalculated columnMeasure
When it is calculatedon refresh, once per rowfor every PivotTable cell, according to its filters
Example=Sales[Qty]*RELATED(Products[Price])Total Sales := SUM(Sales[Amount])
Where it is usedRows, Columns, filters (to slice by)only Values
Memorymakes the file biggertakes almost no space
Baku Sales := CALCULATE([Total Sales], Customers[City] = "Baku")

Where

[Total Sales]
an existing measure: SUM(Sales[Amount])
CALCULATE(expr, filter…)
evaluates the expression in a modified filter context: it replaces the city filter with “Baku” and keeps the other filters

To create a measure: Power Pivot › Calculations › Measures › New Measure… (or right-click the table in the PivotTable Fields pane › Add Measure…).

Three measures in filter context

Problem

The model has 6 sales: C1 (Baku) — Laptop 1200, Lamp 80; C2 (Ganja) — Phone 650, Laptop 1150; C3 (Baku) — Chair 300, Phone 700. Laptop and Phone are Electronics, Chair and Lamp are Home. The PivotTable has Products[Category] in rows. What will [Total Sales], [Baku Sales] and [Baku Share] := DIVIDE([Baku Sales],[Total Sales]) show on each row?

Show solutionHide solution

Solution

Electronics row (filter: Category = Electronics):
[Total Sales] = 1200 + 650 + 1150 + 700 = 3700; [Baku Sales] = 1200 + 700 = 1900; share = 1900 / 3700 ≈ 51.35%.
Home row: [Total Sales] = 300 + 80 = 380; [Baku Sales] = 380 (both are in Baku); share = 100%.
Grand total: 4080; Baku 2280; share ≈ 55.88%.
CALCULATE changed only the city filter, while the category filter stayed — that is the essence of filter context. If the denominator is 0, DIVIDE returns a blank instead of an error.
Text
Total Sales := SUM ( Sales[Amount] )
Total Cost := SUMX ( Sales, Sales[Qty] * RELATED ( Products[UnitCost] ) )
Margin % := DIVIDE ( [Total Sales] - [Total Cost], [Total Sales] )
Baku Sales := CALCULATE ( [Total Sales], Customers[City] = "Baku" )
Share of All Cities := DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Customers[City] ) ) )
A typical set of measures. SUMX calculates row by row and then adds up (the correct fix for the “Price × Quantity” trap from the PivotTable lesson), and ALL removes the city filter entirely — that gives the denominator for a share.
Turn a range into a named Table — the model's first stepCtrl+T
Refresh the active PivotTableAlt+F5
Refresh everything, including the Data ModelCtrl+Alt+F5

Remember

  • The Data Model keeps tables separate and joins them with one-to-many relationships — no more VLOOKUP columns.
  • The key must be unique in the dimension table, and the data types must match on both sides.
  • A calculated column works row by row; a measure is evaluated in each PivotTable cell's filter context.
  • CALCULATE changes the filter context, DIVIDE makes division by zero safe, ALL removes a filter.
  • Ratios and shares must always be measures, not calculated columns.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

10 questions. Every correct answer earns XP.

Question 1 / 10

Question 1 / 10. In a relationship between Sales and Products, which side is the “one” side?