Create and manage table relationships — the foundation of multi-table analysis
Relationships connect tables in Power BI so that filtering one table automatically filters related tables. Without relationships, a slicer on "Region" would not filter your sales chart — you would get wrong totals everywhere.
Power BI detects relationships automatically when column names match — but you should always verify them and create them manually when needed. A relationship needs a matching column in both tables (called the key).
There are three types of cardinality in Power BI relationships:
• One-to-Many (1:*) — most common. One product, many sales rows. One customer, many orders.
• Many-to-One (*:1) — same as 1:* from the other side
• One-to-One (1:1) — rare. Two tables with the same grain.
• Many-to-Many (*:*) — avoid if possible. Requires careful handling.
One-to-Many Relationship: Products → Sales
DIM_Products 1
ProductID (PK)
ProductName
Category
Price
filter flows
→
ProductID
FACT_Sales *
OrderID
ProductID (FK)
CustomerID
Amount
Quantity
OrderDate
1 side (Products)
Each ProductID appears exactly ONCE
* side (Sales)
Same ProductID can appear in MANY rows
Examples
Creating a relationship manually
METHOD 1 — Model View drag and drop:
1. Click the chain-link icon (Model View)
2. Find the key column in the dimension table
(e.g., Products[ProductID])
3. Drag it onto the matching column in the fact table
(e.g., Sales[ProductID])
4. A line appears connecting the two tables
5. The "1" side is on Products, the "*" side is on Sales
METHOD 2 — Manage Relationships dialog:
1. Modeling tab → Manage Relationships → New
2. Select Table 1 and its column
3. Select Table 2 and its column
4. Cardinality: One-to-Many (most common)
5. Cross filter direction: Single (most common)
6. Make this relationship active: ✓ checked → OK
VERIFY:
• "1" appears on one side, "*" on the other
• Click a relationship line → both columns highlight
💡 A solid line = active relationship. A dashed line = inactive relationship (used in advanced DAX with USERELATIONSHIP).
Cross filter direction — Single vs Both
SINGLE (recommended for most cases):
Filter flows ONE direction: Dimension → Fact
Products filters Sales ✓
Sales does NOT filter Products ✗
→ Predictable, faster, fewer ambiguity issues
BOTH (bidirectional):
Filter flows both ways
Use only when necessary (e.g., many-to-many)
Can cause ambiguous filter paths and incorrect results
RULE OF THUMB:
→ Start with Single filter direction
→ Only change to Both if a specific visual requires it
→ Never use Both on all relationships by default
Key Points
✓Relationships are created in Model View — the chain-link icon on the left sidebar
✓1:* (One-to-Many) is the correct cardinality for dimension → fact table relationships
✓Single cross-filter direction is the safe default — use Both only when needed
✓A dashed line = inactive relationship — only one active relationship allowed per table pair
✓Delete incorrect auto-detected relationships before creating correct ones
Common Mistakes
✗ Mistake: Setting both relationships to bidirectional by default
✓ Fix: Use Single direction for all standard relationships. Bidirectional can cause incorrect filter propagation.
✗ Mistake: Joining on text columns with different formatting (e.g., "DELHI" vs "Delhi")
✓ Fix: Clean and standardise key columns in Power Query before loading — relationships break on case/space mismatches.
Practice Question
In a relationship between Products (dimension) and Sales (fact) tables on the ProductID column, what cardinality should you set?