What Is a Many-to-Many Relationship in Power BI?
A many-to-many relationship in Power BI occurs when both tables have duplicate values in their key columns — neither side has a unique key. This is useful for scenarios like mapping students to courses or products to categories where a single row on one side can match multiple rows on the other, and vice versa.
When Do You Need a Many-to-Many Relationship?
Many-to-many relationships are needed when data doesn’t fit the standard one-to-many pattern:
- Budget vs. Actuals: A budget table joined to an actuals table where both sides have repeating department + date combinations.
- Multiple assignments: Employees to projects, students to courses, products to categories.
- Aggregated tables: Joining a summarized table to a detailed transaction table with mismatched granularity.
How to Handle Many-to-Many Relationships in Power BI
Two approaches:
- Direct Many-to-Many: Since September 2018, Power BI supports direct many-to-many cardinality. Set cross-filter to Both. Works but can produce unexpected results.
- Bridge table (recommended): Create an intermediate table with unique key combinations. Each original table connects to it via one-to-many. More controlled filter flow.
How to Avoid Many-to-Many Relationships
- Check for duplicate keys: Clean the dimension table in Power Query to restore unique keys.
- Change granularity: Aggregate one table to match the other’s grain.
- Use a bridge table: Convert many-to-many into two one-to-many relationships.
Many-to-One Relationship in Power BI
Many-to-one is a one-to-many relationship viewed from the opposite direction. From Products: one-to-many. From Sales: many-to-one. Power BI treats them identically.
How to Handle a Many-to-Many Relationship in Power BI
This tutorial shows two approaches to many-to-many relationships — direct cardinality and a bridge table — so you can pick the right one. About 6 steps.
Prerequisites
- Power BI Desktop installed
- Two tables with a many-to-many relationship (e.g., Budget and Actuals sharing Department and Month)
Approach A: Direct Many-to-Many Cardinality
Step 1 — Load both tables and switch to Model View.
Step 2 — Create the relationship. Drag the shared column; set Cardinality to Many-to-Many.
Step 3 — Set cross-filter to Both. Test a visual to verify totals.
Approach B: Bridge Table (Recommended)
Step 4 — Create the bridge table in Power Query. Reference one table, select key columns, remove duplicates.
Step 5 — Connect both tables to the bridge via one-to-many.
Step 6 — Test and verify totals.
