Why your report needs more than one table
In Level 1 you wrote an explicit measure, Total Sales = SUM(Sales[Amount]). On its own it returns one number. It becomes useful when you split it by category, customer or month. Those labels live in other tables, so Power BI needs a path from those tables to Sales. That path is a relationship.
The shape Microsoft recommends for this is the star schema:
- Fact tables record events, such as one row per sale. They hold the numbers you add up, and they are long and full of repeated keys.
- Dimension tables describe the things involved in those events: products, customers, dates. They have one row per thing and a unique key. You use them to filter and group.
Picture a library. The loans ledger is the fact table. The catalogue is a dimension. Each book appears once in the catalogue, but it can appear in the ledger many times.
The data for this lesson
Create these four tables with Home > Enter data, or paste them into Excel and load them:
Product Customer
ProductKey | Product | Category CustomerKey | Customer | City
P1 | Trail Mug | Kitchen C1 | Ana | Leeds
P2 | Camp Stove | Outdoor C2 | Ben | York
P3 | Head Torch | Outdoor
Date
Date | Month
2026-01-05 | Jan
2026-01-20 | Jan
2026-02-03 | Feb
Sales (fact table: one row per order line)
OrderID | Date | ProductKey | CustomerKey | Quantity | Amount
1001 | 2026-01-05 | P1 | C1 | 2 | 20
1002 | 2026-01-05 | P2 | C2 | 1 | 80
1003 | 2026-01-20 | P3 | C1 | 3 | 45
1004 | 2026-02-03 | P2 | C1 | 1 | 80
1005 | 2026-02-03 | P1 | C2 | 4 | 40
If you work it out by hand, Kitchen is 60, Outdoor is 205 and the total is 265. Keep those numbers handy, because they are how you'll prove the model is right.
Walkthrough: build the star in Model view
Open Model view (the diagram icon on the left). When data loads, Power BI may already have created some relationships by matching column names. Check every one it made rather than assuming it got them right.
To create a relationship, drag a key column from one table onto the matching column in the other. You can also use Manage relationships > New. Build these three:
Product[ProductKey] 1 ──► * Sales[ProductKey] -- one product, many sales
Customer[CustomerKey] 1 ──► * Sales[CustomerKey] -- one customer, many sales
Date[Date] 1 ──► * Sales[Date] -- one day, many sales
-- Cardinality: One to many (1:*) | Cross filter direction: Single
Here is how to read the diagram:
- 1 and * at the ends of each line show the cardinality. The "one" side must hold unique values.
- The arrowhead shows which way filters travel. Here they go from the dimension to the fact.
- A solid line is an active relationship and a dashed line is an inactive one.
Double-click any line to open the relationship editor and check the columns and settings.
How a filter actually travels
Put Product[Category] and [Total Sales] in a table visual. For the Outdoor row, Power BI does this:
1. Filter Product to Category = "Outdoor" -> rows P2, P3
2. Follow the relationship using those keys -> ProductKey IN {P2, P3}
3. Keep only the matching Sales rows -> 1002, 1003, 1004
4. Evaluate the measure on what remains -> 80 + 45 + 80 = 205
The result:
Category | Total Sales
Kitchen | 60
Outdoor | 205
Total | 265 <- matches the hand calculation, so the model works
If you add Customer[City] as a second column, both filters reach Sales. Power BI keeps only the rows that satisfy both.
Gotcha: no relationship at all. If you delete the Product–Sales line, every category shows 265. The category filter has no path to
Sales, so the measure sees every row. Whenever you see the same number on every row, check the relationship lines first.
Spotting relationships that will give wrong numbers
1. A dimension key that isn't unique. Suppose someone adds a second row P2 | Camp Stove | Clearance to Product. Power BI can no longer make Product the "one" side. You'll either get the error "One of the columns must have unique values" or be offered Many to many (*:*). If you accept many-to-many, the table visual shows:
Category | Total Sales
Clearance | 160 <- Camp Stove's sales
Kitchen | 60
Outdoor | 205 <- Camp Stove's sales again
Total | 265 <- but the rows add up to 425!
Camp Stove's 160 shows up under two categories, so the rows add up to more than the total. Fix the data so every key appears once. Don't keep the many-to-many relationship.
2. A relationship pointing the wrong way. A many-to-many relationship lets you choose which table filters which. Set it so Sales filters Product, and filters from Category never reach the sales rows. Every row then shows 265 again. The arrowhead should point towards the fact table.
3. Keys that don't match. If a sale has ProductKey = P9 and there is no P9 in Product, a normal one-to-many relationship puts that sale under a (Blank) row. The money is still counted, but it isn't assigned to any category. Many-to-many relationships don't add that blank row, so the unmatched sales disappear from every category but still count in the total.
Best practice: use single-direction, one-to-many relationships from each dimension to the fact table. The other cross-filter setting, Both, has its own surprises. That is the next level.
Key takeaways
- A star schema has fact tables (events and numbers) surrounded by dimension tables (descriptions with unique keys).
- Filters travel along relationships from the "one" side to the "many" side, in the direction of the arrowhead.
- In Model view,
1and*show the cardinality, the arrowhead shows the filter direction, and solid or dashed lines show active or inactive relationships. - The same value on every row means the filter isn't reaching the fact table: there's no relationship, or it points the wrong way.
- A duplicated dimension key that forces many-to-many makes rows add up to more than the total.
- Prove the model works by comparing a simple table visual against totals you worked out by hand.