Handling Many-to-Many Relationships in Power BI

Most Power BI relationships are one-to-many: one product, many sales. Real businesses also have many-to-many links. A bank customer can hold several accounts and an account can have several holders; a salesperson can cover several regions; a song can sit in dozens of playlists. Microsoft's guidance separates three situations, each with a different answer: relating two dimensions, relating two fact tables, and relating facts stored at a higher grain than a dimension (Microsoft Learn: many-to-many guidance).

Many-to-many dimensions: the bridge table

The recommended design keeps each entity as a normal dimension with a unique ID and adds a bridging table with one row per association, related one-to-many to both dimensions. The Chinook sample database has exactly this structure: tracks belong to playlists through a PlaylistTrack table, and sales are recorded per track (Chinook on GitHub).

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
con = sqlite3.connect("chinook.sqlite")
sales = pd.read_sql("SELECT TrackId, UnitPrice * Quantity AS Revenue FROM InvoiceLine", con)
playlist = pd.read_sql("SELECT PlaylistId, Name AS Playlist FROM Playlist", con)
playlist["Playlist"] = playlist["Playlist"].str.replace("’", "'")
bridge = pd.read_sql("SELECT PlaylistId, TrackId FROM PlaylistTrack", con)
print(f"sales lines: {len(sales)} | playlists: {len(playlist)} | distinct playlist names: {playlist['Playlist'].nunique()}")
per_track = bridge.groupby("TrackId").size()
print(f"bridge rows: {len(bridge)} | tracks in the bridge: {per_track.size} | in more than one playlist: {(per_track > 1).sum()}")

sales lines: 2240 | playlists: 18 | distinct playlist names: 14
bridge rows: 8715 | tracks in the bridge: 3503 | in more than one playlist: 3503

Every track sits in more than one playlist, so this is a genuine many-to-many. And 18 playlists share only 14 names: "Music" and "TV Shows", among others, appear twice with different IDs. Relate on the ID, never the name, which is not unique, and make displayed names unique, for example by appending the ID, or users will see two identical rows with no way to tell them apart.

Problem 1: every row shows the grand total

The model is Playlist to PlaylistTrack, Track to PlaylistTrack, and Track to Sales, all one-to-many. With default single-direction filtering, a filter on Playlist reaches the bridge but cannot flow "up" from the bridge to Track, so it never reaches Sales. Microsoft's example shows the symptom: every customer displays the same balance as the total. Here is the same effect with real data:

total = sales["Revenue"].sum()
rows = []
for _, p in playlist.iterrows():
    tracks = bridge.loc[bridge["PlaylistId"] == p["PlaylistId"], "TrackId"]
    rows.append({"PlaylistId": p["PlaylistId"], "Playlist": p["Playlist"],
                 "Filter stops at bridge": round(total, 2),
                 "Filter reaches Sales": round(sales.loc[sales["TrackId"].isin(tracks), "Revenue"].sum(), 2)})
visual = pd.DataFrame(rows).sort_values(["Filter reaches Sales", "PlaylistId"], ascending=[False, True])
print(visual.head(6).to_string(index=False))
print(f"sum of all playlist rows: {visual['Filter reaches Sales'].sum():.2f} | grand total: {total:.2f}")

 PlaylistId   Playlist  Filter stops at bridge  Filter reaches Sales
          1      Music                  2328.6               2107.71
          8      Music                  2328.6               2107.71
          5 90's Music                  2328.6                944.46
          3   TV Shows                  2328.6                220.89
         10   TV Shows                  2328.6                220.89
         12  Classical                  2328.6                 40.59
sum of all playlist rows: 5738.28 | grand total: 2328.60

The fix, per Microsoft's guidance, is to set one relationship to filter in both directions, here the one between Track and PlaylistTrack, so the filter continues from the bridge to Track and on to Sales. The "Filter reaches Sales" column is what the visual then shows. If you prefer to keep every relationship single-direction, enable the direction only inside a measure with CROSSFILTER, which sets the cross-filtering direction for a relationship during one calculation (DAX reference: CROSSFILTER):

Revenue by Playlist =
CALCULATE (
    [Revenue],
    CROSSFILTER ( PlaylistTrack[TrackId], Track[TrackId], Both )
)

Problem 2: the rows add up to more than the total

With filters working, the playlist rows sum to 5,738.28 while the total is 2,328.60. Nothing is wrong: a sale of a track counts towards every playlist that contains the track, so the playlist figures overlap. Microsoft calls such results non-additive, and in its bank example two customers' balances add up to more than the total because one account is joint. The total row correctly means "all sales", not "the sum of the rows above". Microsoft's small bank example shows the same thing in miniature: account 1 has a balance of 75 and is held jointly by two customers, account 2 has 200 and belongs to one of them. The customers' balances are 75 and 275, which add up to 350, yet the bank holds only 275 in total.

Report readers will not know this unless told. Add a subtitle or tooltip such as "A track can appear in several playlists, so playlist figures overlap", and avoid showing these rows as a stacked chart or a pie, which imply parts of a whole.

Where else the bridge pattern applies

The same design fits any association where both sides can have many partners: patients and diagnoses, students and courses, articles and tags, employees and projects, or products that belong to several promotional campaigns. In each case, keep both entities as dimensions with unique IDs, store the pairs in a bridge, and decide in advance how the report should explain totals that overlap.

Relating two fact tables: do not

Two fact tables, such as orders and shipments, can be related directly with a many-to-many relationship, but Microsoft advises against it: visuals can then only group by the shared column, and data integrity problems can silently drop rows. Instead, give both fact tables the same dimensions, such as order line, date and product, related one-to-many. Filters on any shared dimension then reach both facts. This is the star schema principle applied to more than one fact table.

Facts at a higher grain: targets

Sales are recorded per product and day, but targets are often set per category and year. Microsoft's approach relates the target date, stored as the first day of the year, one-to-many to the date table, and relates category through a many-to-many relationship filtering from the product dimension to the target table. Then a measure controls summarisation so targets only appear at the level they were set:

Target Quantity =
IF (
    NOT ISFILTERED ( 'Date'[Date] ) && NOT ISFILTERED ( 'Date'[Month] )
        && NOT ISFILTERED ( 'Product'[Product] ) && NOT ISFILTERED ( 'Product'[Color] ),
    SUM ( Target[TargetQuantity] )
)

Without it, a monthly view shows the whole year's target in January, and grouping by colour shows targets for every category containing that colour, both misleading.

Checklist

1. Give each entity its own dimension table with a unique ID.
2. Add a bridge table with one row per association, and hide it.
3. Relate everything one-to-many, and set exactly one relationship to both directions, or use CROSSFILTER in measures.
4. Hide ID columns on the many side, and keep visible names unique.
5. Explain non-additive totals on the report.
6. Relate fact tables through shared dimensions, never directly.
7. Control higher-grain facts with measures that return blank below their grain.

Relationships and cardinality covers the relationship settings in detail, and DAX CALCULATE explained covers how CROSSFILTER fits among CALCULATE's modifiers.

Relationships between tables and data modelling best practices are part of the Power BI course.

Sources

• Many-to-many relationship guidance (Microsoft Learn) — Microsoft Learn
• CROSSFILTER function (DAX reference, Microsoft Learn) — Microsoft Learn
• Chinook sample database (GitHub) — GitHub