Day 4 — The data model and relationships

Get the model right and DAX becomes easy. Get it wrong and no amount of clever DAX will save you.

Fact and dimension

Two kinds of table, and telling them apart is most of the skill.

	Fact table	Dimension table
Holds	Events — things that happened	Descriptions — things that are
Example	Sales, transactions, tickets	Product, Region, Calendar, Customer
Size	Many rows, few columns	Few rows, many columns
You mostly	Aggregate it	Slice and group by it

A quick test: if a column would make a sensible slicer, it belongs in a dimension. Nobody slices by transaction amount; everybody slices by category.

The star schema

Fact in the middle, dimensions around it, each joined to the fact by a key. Drawn out, it looks like a star — hence the name.

Why this shape and not a flat table

The product name is stored once, not repeated on every sales row
One slicer filters every measure that touches the fact
Filters travel a short, predictable path, so DAX stays simple
The compression engine works far better on narrow tables

One to many

Every relationship here is one-to-many: one row in Product relates to many rows in Sales.

Product[ProductID] 1 → * Sales[ProductID] Region[RegionID] 1 → * Sales[RegionID] Calendar[Date] 1 → * Sales[Date]

The one side must be unique. If ProductID repeated in the Product table, Power BI would refuse to create the relationship — and it would be right to, because the answer would be ambiguous.

Filters flow one way: down the arrow

This is the single most useful fact about the model. Filtering a dimension filters the fact. Filtering the fact does not filter the dimension.

Category lives on the Product table, and filtering it changed the Sales total. That is the arrow doing its job — no lookup formula anywhere.

See it from the other side

Look at the second column It says 6 on every row. Filtering by Region filtered Sales, but it did not filter Product — there is no relationship from Region to Product, and filters do not flow uphill from the fact table anyway. This is not a bug; it is the arrow direction, made visible. When a number refuses to change as you slice, this is almost always why.

The date table

Every model needs a proper Calendar table with one row per day and no gaps. The one here covers every day of 2024 and 2025.

Do not use the date column on your fact table Sales only has 48 dates — the days something happened. A month with no sales would simply vanish from a chart rather than showing zero, and time intelligence needs a continuous run of dates to work at all. A dedicated Calendar table fixes both problems, which is why Day 9 depends on it.

Slice by two dimensions at once

Region comes from the visual, Category from the measure, and both reach the fact table through their own relationships. The two columns add up to the first on every row. That is the star schema working.

Try these yourself

Decide whether a Customer table is a fact or a dimension, and say why.
Explain why the "one" side of a relationship must be unique.
Break sales down by Region[Manager] instead of Region.
Explain why COUNTROWS(Product) does not change when you slice by Region.
Give two reasons a model needs its own Calendar table.

← PreviousDay 3 — Shaping data — split, group, append and merge Next →Day 5 — Measures and calculated columns