Data Model and relationships — multiple tables without VLOOKUP

1. What is the Data Model?

A compressed database inside the workbook (the same engine as Power BI). It holds millions of rows in a small file, links tables with relationships, and lets you write DAX measures. Power Pivot is the window for managing it.

Availability: Excel for Microsoft 365 / 2016+ on Windows. Turn on the window: File → Options → Add-ins → Manage: COM Add-ins → Go → tick Microsoft Power Pivot for Excel. (Excel for Mac can't open the Power Pivot window.)

2. Load tables into the model

From Module 3, set the capstone queries to Close & Load To… → Only Create Connection + Add this data to the Data Model:

(For plain Excel Tables: Power Pivot tab → Add to Data Model.)

3. Create relationships

Power Pivot tab → Manage → Diagram View. Drag fact_Sales[ProductID] onto dim_Products[ProductID], and fact_Sales[StoreID] onto dim_Stores[StoreID].

Each line shows 1 on the dimension side and * (many) on the fact side — one product, many sales. The filter arrow points from dimension to fact: picking "Electronics" in Products filters Sales.

Rules:

• The "one" side must have unique keys (no duplicates, no blanks).
• Key columns must have the same data type and identical values (trim spaces first — Module 3).
• Only one active path between two tables.

4. Add a Calendar table

Time analysis needs a table with every date in the range. In Power Pivot: Design → Date Table → New creates Calendar covering your data's years. Add Indian FY columns as calculated columns (Lesson 2 explains these):

FY      = IF(MONTH([Date]) >= 4, "FY" & YEAR([Date]) & "-" & RIGHT(YEAR([Date]) + 1, 2), "FY" & YEAR([Date]) - 1 & "-" & RIGHT(YEAR([Date]), 2))
FY Month No = MOD(MONTH([Date]) - 4, 12) + 1

Then Design → Date Table → Mark as Date Table (Date column), and relate fact_Sales[OrderDate] → Calendar[Date]. Sort the Month name column by month number (Home → Sort by Column) so months don't sort alphabetically.

The result is the star schema from Module 1:

dim_Products ─┐
dim_Stores  ──┼──> fact_Sales
Calendar    ──┘

5. One pivot, many tables

Insert → PivotTable → From Data Model. The field list now shows every table. Build:

• Rows: dim_Stores[Region]
• Columns: dim_Products[Category]
• Filter: Calendar[FY] = FY2025-26
• Values: a measure (Lesson 2)

No XLOOKUP columns, no wide merged table — and 50,000 rows refresh in seconds.

6. Bonus features from the model

• Distinct Count in Value Field Settings (e.g. number of different products sold).
• One slicer filters pivots built on different fact tables, as long as they share dimensions.
• CUBEVALUE formulas can read any number from the model into a cell report.

Common mistakes

Relationship created in the wrong direction (fact → fact, or dimension keys not unique — Excel refuses with "duplicate values"). Using fields from the fact table (e.g. fact_Sales[StoreID]) in pivots instead of dimension fields. Forgetting to mark the Date table.