Tutorials › Power BI › Relationships in Power BI

Relationships in Power BI

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?

Related Topics

The Data Model in Power BIWhat a data model is and why it is the foundation of every good Power BI reportStar Schema DesignDesign an efficient data model using star schema — the industry standard for Power BIFILTER, ALL and ALLEXCEPTControl and remove filter context with FILTER, ALL, ALLEXCEPT and ALLSELECTED