Relationships & Cardinality
Power BI · Data Modeling
Theory
A relationship links a column in one table to a column in another so filters can flow between them. Cardinality describes how many rows match on each side. One-to-many (1:*) is the normal case: each CustomerID appears once in Customers but many times in Sales. One-to-one (1:1) means both sides are unique, which often signals the tables could be merged. Many-to-many (*:*) means neither side is unique, for example relating Sales[Region] to a Targets table that also repeats regions; it works but needs care because totals can surprise you.
Only one active relationship can exist between two tables; others are inactive (dashed lines) and can be used in a measure with USERELATIONSHIP. With Customers 1:* Sales in place, putting Customers[City] on a table visual with [Total Sales] gives Jaipur ₹2,700 (Asha's two orders), Pune ₹2,400, Chennai ₹600 and Delhi ₹500.
Why we use it
Relationships let a filter on one table affect another, so you never need lookup formulas to bring City into Sales. Correct cardinality keeps totals accurate and the engine efficient.
When to use it
Use one-to-many from every dimension to its fact table as the default. Investigate any one-to-one or many-to-many suggestion: often a key has duplicates or blanks that should be fixed, or a bridge or dimension table is missing.
How it works
- Need to link two tables
- Drag the key column onto the matching column
- Is the key unique on one side? Yes: Power BI sets one-to-many. No: Fix duplicates or plan a many-to-many design.
- Set cross-filter direction (Single by default)
- Test with a visual: dimension column + measure
- Filters now flow between the tables
Syntax
Model view → drag column onto column, or Home → Manage relationships → New
Cardinality: Many to one (*:1) | One to one (1:1) | One to many (1:*) | Many to many (*:*)
Cross filter direction: Single | Both
Inactive relationships: use USERELATIONSHIP(col1, col2) inside CALCULATEExample
1. Manage relationships → New: Sales[CustomerID] → Customers[CustomerID].
2. Power BI detects Many to one (*:1) because CustomerID is unique in Customers.
3. Cross filter direction: Single (Customers filters Sales).
4. Table visual: Customers[City], [Total Sales]
Jaipur ₹2,700 | Pune ₹2,400 | Chennai ₹600 | Delhi ₹500
5. If detection says many-to-many, check Customers for a duplicate CustomerID.Key points
- One-to-many is the standard dimension-to-fact relationship
- Key columns should match in data type and have no blanks on the 'one' side
- Only one active relationship per pair of tables; others are inactive
- Many-to-many is valid but often hides a modeling problem
- Filters flow from the 'one' side to the 'many' side by default