4.4 The data model and a first taste of DAX

What you'll be able to do

Relate two tables once, instead of writing a hundred lookups. Write your first measures, which are named calculations that let PivotTables answer questions about rates and ratios correctly. This is where Power BI begins, met inside Excel.

Before you start

Open the lookup-and-join workbook. It holds a table of events and a table of details, which is exactly the shape the data model wants. This topic needs Microsoft 365 Excel installed on a computer. It is one of the five paid topics named in Getting Your Footing.

The work

1. Same either way: name the old way first. To analyse sales by student details, the lookup module copied those details onto every sales row: thousands of XLOOKUP formulas, refreshed for ever. The model way loads both tables and declares the relationship once, and nothing is copied anywhere.
2. No documented keyboard shortcut for this — use the menu: load each table with Data → From Table/Range. In the editor, choose Close & Load To → Only Create Connection, and tick Add this data to the Data Model. The tables go into the model, which is a store held in memory beside the grid, instead of onto sheets.
3. No documented keyboard shortcut for this — use the menu: now relate them. Go to Data → Relationships → New and join the key column of the events table to the key column of the details table. This is the star shape. One big table of events, such as sales or marks, sits surrounded by small tables of things, such as students or products. Every business intelligence tool assumes this layout, and you are meeting it here at two-table size.
4. Same either way: build a PivotTable from the model (Insert → PivotTable → From Data Model). The field list now shows both tables. Take rows from the details table and values from the events table, and the relationship carries the connection between them. The hundred lookups you did not write are the payoff.
5. Same either way: measures come next, and the reason comes first. Drop a ratio column such as margin per cent into Values, and the PivotTable averages the row percentages. That is wrong wherever the rows differ in size. A measure works the number out at whatever level the PivotTable is showing: total profit over total sales, in every cell. Ratios are always measures. A calculated column is for labels that a row genuinely owns.
6. No documented keyboard shortcut for this — use the menu: write one. In the PivotTable field list, right-click the events table and choose field list → Add Measure. Name it Total Sales, with the formula =SUM(Sales[Amount]). Then learn the pattern that carries furthest: =DIVIDE([Total Profit], [Total Sales]), where DIVIDE is used because it survives a zero on the bottom. And learn to read CALCULATE when you meet it: =CALCULATE([Total Sales], Fees[Status]="Paid") is the same measure under a changed filter. CALCULATE is the whole trick of DAX, and Power BI is this vocabulary at full size.

Try it

Load both practice tables to the model and relate them on the key. Build a PivotTable that crosses both tables, with no lookups at all. Write Total Sales as a measure, then a DIVIDE ratio, and watch the ratio stay honest at every level of the PivotTable.

Check yourself