Advanced Analytics›Pro· 50 min read

The Data Model & Intro to DAX

Relate two tables without VLOOKUP, then write your first DAX measure — the bridge from spreadsheets to business intelligence.

What you will learn

Explain what the Data Model and a relationship are
Connect two tables on a shared column
Write a simple DAX measure with CALCULATE

When one table is not enough

Real data rarely lives in one table. You might have an Orders table (one row per sale) and a separate Products table (one row per product, with its category). The old way to combine them is a VLOOKUP that copies the category onto every order — slow and fragile on big data. The modern way is the Data Model: you load both tables and tell Excel how they relate, then analyse them together as if they were one.

This engine is called Power Pivot. It lets a pivot table draw from several related tables at once, handles far more rows than a normal sheet, and lets you write calculations in a language called DAX. It is the same engine that powers Power BI, so learning it here is a genuine step toward business-intelligence work.

Watch out: Power Pivot and the Data Model live in Excel for Windows (Microsoft 365 and many 2016+ editions) and recent Excel for Mac. They are an Excel-desktop feature; this lesson is the advanced bridge topic, so do not worry if your edition lacks it — the rest of the course works everywhere.

The two tables

Here is the idea in miniature. An Orders table records sales by product code, and a Products table maps each code to a category. The shared column is ProductCode — that is the link between them.

Orders: OrderID	ProductCode	Sales
1	A	120
2	B	80
3	A	100
4	C	60

Products: ProductCode	Category
A	Fruit
B	Fruit
C	Dairy

Step 1 — load both tables and relate them

You add each table to the Data Model and then draw a relationship between them on the shared column. A relationship is just Excel knowing “ProductCode in Orders points to ProductCode in Products”, so it can look across on its own.

Make each block an Excel Table (Ctrl + T), naming them Orders and Products.
For each, choose Data > Add to Data Model (Power Pivot). Both tables now live in the model.
Open Power Pivot > Manage > Diagram View, then drag ProductCode from Orders onto ProductCode in Products. A line appears — that is the relationship.

Note: Output: The diagram shows two boxes joined by a line from Orders[ProductCode] to Products[ProductCode]. Excel now understands the two tables are linked, so a pivot can group Orders by the Category that only exists in the Products table — no VLOOKUP needed.

Step 2 — a pivot across both tables

Now insert a pivot from the Data Model. You can drag Category (from Products) into Rows and Sales (from Orders) into Values, even though they live in different tables — the relationship joins them for you.

One pivot pulling fields from two related tables at once

Rows:    Category   (from Products)
Values:  Sum of Sales   (from Orders)

Note: Output: Fruit 300 Dairy 60 Grand Total 360 Product A and B are both Fruit, so their sales (120 + 100 + 80) totalled 300; product C is Dairy, giving 60. The pivot followed the relationship to group orders by a category that was never in the Orders table itself.

Step 3 — your first DAX measure

DAX (Data Analysis Expressions) is the formula language of the Data Model. A measure is a reusable named calculation written in DAX — like a smart formula that recalculates for whatever row, filter or slicer the pivot is showing. You add one with Power Pivot > Measures > New Measure.

A measure looks a lot like an Excel function, but it works on whole columns of a table. The simplest one totals a column:

A DAX measure that sums the Sales column of the Orders table

Total Sales := SUM(Orders[Sales])

Note: Output: Dropped into the pivot beside Category, “Total Sales” shows 300 for Fruit and 60 for Dairy — the same totals, but now as a named, reusable measure you can use in any pivot built on this model.

The real power of DAX is CALCULATE, which works out an expression under a filter you specify. Here is a measure that totals only the Fruit sales, no matter what the pivot is filtered to:

CALCULATE totals Sales but forces the filter Category = Fruit

Fruit Sales := CALCULATE(SUM(Orders[Sales]), Products[Category] = "Fruit")

Note: Output: 300 CALCULATE took the basic total SUM(Orders[Sales]) and applied the extra filter Category = Fruit, so it added only the Fruit orders (120 + 100 + 80) to 300 — ignoring Dairy even if Dairy is showing elsewhere. CALCULATE is the heart of DAX: “do this sum, but under that condition.”

Spreadsheet way	Data Model way	Why the model wins
VLOOKUP category onto every row	A relationship between two tables	No copied columns; faster on big data
SUMIFS per question	One reusable DAX measure	Write once, use in any pivot
One sheet, ~1M row limit	Millions of rows in the model	Scales far beyond a worksheet

Tip: Think of a measure as a verb (an action that recalculates with the pivot) and a normal column as a noun (a fixed value per row). Measures like Total Sales and CALCULATE-based ones are the building blocks of every Power BI report, so this lesson is your on-ramp to BI.

Q. In the Data Model, what does a relationship between the Orders and Products tables let you do?

Answer: A relationship links the tables on a shared column (ProductCode), so a pivot can use fields from either table together — for example grouping Orders by the Category stored only in Products — without copying data across with VLOOKUP.

✍️ Practice

Build the Orders and Products tables, add both to the Data Model, and create a relationship on ProductCode.
Make a pivot of Sales by Category from the model, then add a “Total Sales” DAX measure.

🏠 Homework

Create a small two-table model (for example Sales and Customers linked by CustomerID). Relate them, build a pivot that groups sales by a column from the second table, and write one CALCULATE measure that totals sales for a single chosen group.