2.1Relationships & data model
The essentials in 30 seconds
- Relationships link two tables on a common column (Products[Code] ↔ Sales[ProductCode]): the model propagates the filters, with no formula at all.
- Normal cardinality: one-to-many (1 → *), from the reference table (the "1" side) to the facts (the "*" side); the single filter direction is the healthy norm.
- Diagnosis No. 1: identical totals on every row of a visual = missing or broken relationship — always check auto-detected relationships.
Running mini-case — Zineb links her sales to the product reference table
Zineb, a management controller in an agri-food SME in Algiers, needs to cross her sales by product category, but her Sales and Products tables each live on their own. She applies the lesson: in the Model view, she drags Sales[ProductCode] onto Products[Code] — the line appears —, reads the 1:* cardinality and the single filter direction, then proves with a bar chart Category × Amount that the two tables talk to each other. To see the symptom, she deletes the relationship: every total becomes identical across each category. She recreates it at once and remembers diagnosis No. 1 forever.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 2.1.
1. Linking the tables
Decisive level: the model. Your real data arrives in several tables (sales, products, customers) that Power BI links with relationships: a line between two tables on a common column. Once the relationship is set, a visual crosses "Product name" and "Sales amount" with no formula at all — the model propagates the filters. Zineb thus replaces her old VLOOKUPs: the filter flows from one table to the other on its own.
2. Cardinality & direction
We read the cardinality (one-to-many in the vast majority of cases: ONE product ↔ SEVERAL sales) and the filter direction (from the "1" side to the "*" side). Single direction is the healthy norm; bidirectional creates ambiguity and slowness. Auto-detected relationships on import must always be verified in the Model view. Zineb double-clicks the line to read 1:* and "single" before concluding.
3. Diagnosis No. 1
The classic symptom of a missing or broken relationship: identical totals on every row of a visual — each category shows the same total. You recognise it for life. Another cause of a 1:* relationship failing, on the "1" side: duplicates or blanks in the key column, because a dimension has a single row per element. Zineb then fixes it in Power Query (Remove duplicates, filter nulls).
🔎 Concrete example — 🔎 Concrete example — Sales ↔ Products
Zineb crosses category and amount, two linked tables:
Relationship: Sales[ProductCode] → Products[Code] (1:*)
→ visual Category (from Products) × Amount (from Sales)
the two tables talk to each other, with no formula.
Delete the relationship → identical totals everywhere (the symptom).Reading: the 1:* cardinality is carried by the unique key on the dimension side (Products[Code]).
Lesson: deleting the relationship makes identical totals appear — the diagnostic reflex to recognise.
Section vocabulary
- Relationship
- The link between two tables on a common column (Products[Code] ↔ Sales[ProductCode]): the channel through which filters flow.
Simple example: Zineb links Sales and Products to cross category and amount.
What it is not: two tables placed side by side, with no line between them.
Reflex: two tables to cross → a relationship on the key column.
- One-to-many cardinality (1:*)
- The standard case: ONE product of the reference table ↔ SEVERAL sales rows.
Simple example: Zineb reads 1:* on the line between Products and Sales.
What it is not: one-to-one or many-to-many, reserved for other cases.
Reflex: 1:* from the "1" side (dimension) to the "*" side (facts).
- Filter direction
- By default, the filter goes from the "1" side to the "*" side: filtering a product filters its sales.
Simple example: Zineb keeps the single direction, with no ambiguity.
What it is not: bidirectional mode, a source of slowness and bugs.
Reflex: single direction by default; bidirectional = very special case.
- Auto-detection
- Power BI guesses some relationships on import: to be checked systematically.
Simple example: Zineb checks every auto-detected relationship in the Model view.
What it is not: a relationship assumed to be good without verification.
Reflex: auto-detected relationship → always check cardinality and direction.
- Key column
- The common column that carries the relationship: unique on the dimension side, repeated on the facts side.
Simple example: Zineb makes sure Products[Code] is unique.
What it is not: a key with duplicates or blanks that blocks the 1:*.
Reflex: unique key on the "1" side: clean in Power Query if needed.
In practice — In practice — Linking sales and the product reference table
- Import a second table "Products" (Code, Name, Category, UnitPrice) — create it in Excel if needed, like Zineb.
- Model view: if Power BI has not created the relationship, drag Sales[ProductCode] onto Products[Code] — the line appears.
- Double-click the line: read the cardinality (1:*) and the filter direction (single) — the healthy default configuration.
- Proof by the visual: create a bar chart Category × Amount. Delete the relationship to SEE the symptom (same totals everywhere), then recreate it.
Step-by-step script — link like Zineb (5 min)
1) Import the Products table alongside Sales. 2) In the Model view, drag Sales[ProductCode] onto Products[Code]: the line appears. 3) Double-click the line and read the cardinality (1:*) and the direction (single). 4) Create a bar chart Category × Amount and verify that the two tables talk to each other. 5) Delete the relationship, observe the identical totals everywhere, then recreate it — diagnosis No. 1 is anchored.
If the relationship refuses the 1:*, look for duplicates or blanks in the key on the dimension side and clean them in Power Query.
Copy-paste template — my relationship check-list
Relationship: Sales[ProductCode] → Products[Code] (1:*)
[ ] Unique key column on the dimension side (no duplicate, no blank)
[ ] Cardinality read: 1:*
[ ] Filter direction: single (1 → *)
[ ] Auto-detected relationship checked in the Model view
Diagnosis: identical totals everywhere → missing or broken relationship.Cross category and amount
Zineb links Sales and Products: the visual Category × Amount works without a formula.
Replace a VLOOKUP
Zineb no longer writes a lookup: the relationship propagates the product name from the dimension.
Diagnose a frozen total
All totals identical per category? Zineb immediately checks the relationship in the Model view.
Clean a key
A key with duplicates blocks the 1:*: Zineb removes the duplicates on the dimension side in Power Query.
✓ Best practices
- Link the tables by the key column (unique on the dimension side).
- Keep the single filter direction (1 → *): the default reflex.
- Check every auto-detected relationship in the Model view.
✗ Common pitfalls
- Leaving a false auto-detected relationship without checking.
- Switching to bidirectional "to make it work" (ambiguity, slowness).
- A key on the dimension side with duplicates or blanks (blocks the 1:*).
Key takeaways
- Relationships replace VLOOKUPs: the model propagates filters between tables.
- Normal cardinality: one-to-many, from the reference table (1) to the facts (*).
- Always check auto-detected relationships in the Model view.
- Identical totals on every row of a visual = missing or broken relationship: diagnosis No. 1.
48h challenge — apply it tomorrow
Like Zineb, link two tables with a relationship: drag the key from the facts onto the key of the dimension in the Model view, read 1:* and the single direction, then create a visual crossing the two tables. Delete the relationship and note the symptom (identical totals everywhere) before recreating it.
- I can create a relationship between two tables by their key column.
- I read the cardinality (1:*) and the filter direction (single) of a relationship.
- I recognise diagnosis No. 1: identical totals = missing relationship.
Frequently asked questions
When should I use a bidirectional relationship?
Almost never: it creates calculation ambiguities and slowness, and shows up as bugs that are hard to diagnose. Legitimate cases exist (certain filterings by a dimension shared between two fact tables), but they are handled first by a good star schema (2.2) or, occasionally, by the DAX function CROSSFILTER. The default reflex remains the single direction.
My relationship refuses the 1:* cardinality: why?
Because the key column on the dimension side contains duplicates or blanks, whereas a dimension has a single row per element. Fix it in Power Query: Remove duplicates on the key, filter out null values. A clean reference table (a single unique row per element) is the condition for a reliable model.
What does "identical totals on every row" of a visual mean?