📎 This article includes 2 downloadable practice files ↓
In this article
Relationships are how a click on “North” knows which orders to sum. Get them right once and every visual works; get them wrong and totals repeat or go blank.
Create relationships
In Model view, drag Products[ProductID] onto Sales[ProductID]. Repeat: Customers[CustomerID] → Sales[CustomerID], Calendar[Date] → Sales[OrderDate]. Power BI may have auto-detected some; check each one by double-clicking the line.
Cardinality
Dimension to fact is one-to-many (1:*): one product, many orders. The “1” side must have unique values. If Power BI suggests many-to-many, there’s a duplicate key in a dimension; fix the data rather than accepting it.
Cross-filter direction
- Single (default, recommended): filters flow from the dimension to the fact. Picking a Region filters Sales.
- Both: filters also flow back. Sometimes needed (show only products a customer bought in a slicer), but it causes ambiguity and slow models when overused.
Test the relationship
Make a table visual with Products[Category] and a Sales total. Each category should show a different number. If every row shows the same total, the relationship is missing or inactive.
The (Blank) row
A “(Blank)” category in a visual means some Sales rows have a ProductID that doesn’t exist in Products. Find them with a Left Anti merge in Power Query and fix the master.
Inactive relationships
Only one active path is allowed between two tables. If Sales has OrderDate and DeliveryDate, both related to Calendar, one becomes dashed (inactive). Use it in a measure with USERELATIONSHIP:
Sales by Delivery Date =
CALCULATE ( [Total Sales], USERELATIONSHIP ( Calendar[Date], Sales[DeliveryDate] ) )Where beginners go wrong
| Symptom | Cause |
|---|---|
| Same number on every row | No (active) relationship to that table |
| (Blank) category | Keys missing from the dimension |
| Many-to-many warning | Duplicate keys in a dimension |
| Slow, confusing results | Too many bidirectional filters |
Practice
Download the data model and expected results below. Load all four tables into Power BI Desktop and build this lesson’s steps; compare your numbers with the expected-results workbook.
📎 Practice files for this article
- 📗Practice data model (Excel)Four tables: Sales (600 orders, FY 2024-25 and 2025-26), Products, Customers and a Calendar with FY columns.⬇ XLSX · 40 KB
- 📗Expected resultsThe values your measures and visuals should show.⬇ XLSX · 7 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.