In Power BI, when should you use a bridge table instead of a many-to-many cardinality relationship?

## The situation You have two tables whose join column repeats on both sides. Classic cases: `Sales` at daily/product grain and `Budget` at monthly/category grain, both carrying `MonthKey`; or `Customer` and `Account` where a customer holds several accounts and an account is held jointly by several customers. Power BI offers two answers, and the interview question is which one and why. ## Option A: many-to-many cardinality Select `Many to many (*:*)` in the relationship dialog and relate the two tables directly on the repeated column. It works immediately and requires no new tables. What you have created, though, is a **limited relationship** (the engine's term; also called a weak relationship), and limited relationships differ from regular ones in ways that produce wrong numbers rather than errors: - **No blank row.** A regular relationship materialises a blank member on the one side so unmatched or null many-side keys still land somewhere and the grand total stays whole. A limited relationship does not. Unmatched rows are excluded from cross-filtered results but still counted in an unfiltered total, so the visible rows stop summing to the total and nothing on the page explains the gap. - **`RELATED` does not work across it.** Row-context lookups need a regular relationship, so a calculated column that walks the relationship simply cannot be written. - **No shared-key entity.** Users have no table to slice by. If they select a month, which table's month are they selecting? Whichever one you exposed — the other side is filtered only indirectly. Relationships that span source groups in a **composite model** (for example an import table related to a DirectQuery table) are limited for the same reason, and inherit the same caveats. ## Option B: a bridge table Build a table of the **distinct values** of the shared key and relate it one-to-many to each side. Both relationships are now regular, and the bridge itself becomes the thing users filter on: ```text Month (bridge: distinct MonthKey, MonthName, Year) 1 1 | | v v Sales Budget (many rows) (many rows) ``` Now a slicer on `Month[MonthName]` filters both fact tables identically, each relationship gets blank-row protection, and `RELATED` works from either fact table into the bridge. For two fact tables at different grains this is simply the conformed-dimension pattern expressed in Power BI: relate both facts to a shared dimension **at the coarsest common grain** rather than to each other. Note that filtering flows *down* from the bridge to both sides with the default single direction — which is exactly what you want for two fact tables. The genuine many-to-many case (customers and accounts) needs one more piece: a bridge holding the *pairs*, sitting between the two dimensions. Because a filter must travel up one relationship and down the other, that pattern needs bidirectional propagation across the bridge — either set on the relationship or, more surgically, applied per measure with `CROSSFILTER`. ## Choosing Use a **bridge** when: the shared key is a business entity people want to see and slice (dates, months, categories, accounts); you need `RELATED`; you need blank-row protection and totals that reconcile; more than two tables share the key; or the model will be consumed by self-service authors who did not build it. Use **many-to-many cardinality** when: the join is small and contained, you have verified both key sets match, no calculated column needs `RELATED`, and adding a table would be ceremony. It is a legitimate tool — Microsoft added it precisely because bridges plus bidirectional filters were being over-used — but it is a considered choice, not a default. ## The mistake to avoid entirely Do not relate two fact tables **directly** because they happen to share a column. `Sales` joined straight to `Budget` on `MonthKey` gives every sales row a partner for every budget row of that month, and any measure that spans both starts multiplying rows. The symptom is a number that is wrong by a suspiciously round factor. Facts talk to each other **through** dimensions, never directly — that is what the star schema is for, and Power BI's relationship engine assumes it. ## What interviewers listen for The answer they want names the limited-relationship behaviour explicitly — blank rows, `RELATED`, totals not reconciling — rather than saying bridges are "best practice". A senior answer also separates the two distinct scenarios that both look like many-to-many: *two facts at different grains* (solved by a conformed dimension) and *two dimensions with a genuine pair-wise relationship* (solved by a bridge of pairs plus scoped bidirectional propagation).