Key takeaways
- Model dim A to dim B many-to-many with an explicit bridge or fact grain statement.
- Promote bridge keys to first-class columns and validate uniqueness and orphans in gold.
- Share matching and enrichment logic across fact tables so headline counts stay honest.
- Align foreign key types on every role-playing relationship endpoint.
- Prove remodel acceptance with fixtures before rebuilding report pages.
- Deprecate convenience views that hide grain, with a real removal date.
Helpful gold views that "just join dim A and dim B" hide many-to-many grain until a report page disagrees with itself. Filters double count. Role-playing dims break. Two pages that should reconcile never do.
Deploy bridge keys as a first-class artifact and remodel the fact table before you polish visuals. Matching logic must be shared across every fact table that uses it or the numbers diverge by construction. Semantic role-playing dims need matching foreign key types. Remodel first. Visual polish second.
Named model: bridge keys before dashboards
- Business M:M: rows of dim A per dim B and rows of dim B per dim A are both first-class questions, not accidents of a flat join.
- Explicit bridge / fact: a grain sentence, first-class bridge keys, uniqueness and orphan tests, shared matching, and aligned FK types in gold.
- Trusted pages: dim A and dim B slices reconcile under the same filters because the model stores the relationship you already have in the business.
- Rule: convenience views are not a grain strategy. Dashboards inherit whatever grain you leave in gold.
Figure 1. Bridge keys before dashboards. Name the many-to-many, store it as a bridge or honest fact, then polish. Convenience views are not a grain strategy.
Context and constraint
A common failure: a page built on dim A and a page built on dim B both look plausible and refuse to reconcile in the same week. The gold layer has a convenience view that flattens a many-to-many into something that looks one-to-many on a good day. Dashboards are not the bug. The grain is.
Bridge keys belong in gold with validation, not as DAX that "fixes it in the report." Shared matching across fact tables keeps headline counts honest. Role-playing dimensions multiply the cost of one wrong key type. Report polish on a broken grain trains users to distrust every number.
Figure 2 is the Learn surface for relationship cardinality. Many-to-many grain leaks into blank or double-counted visuals when you pretend a flat view is enough.
Figure 2. Many-to-many grain leaks into blank or double-counted visuals. Remodel the fact before polishing pages. Source: Microsoft Learn: understand relationships.
Figure 3 is filter direction on the same Learn page. Bridge keys and filter direction are part of the remodel. Dashboards inherit whatever grain you leave in gold.
Figure 3. Bridge keys and filter direction are part of the remodel. Source: Microsoft Learn: understand relationships.
Design
1. Name the grain out loud
Write whether a row is an event, an assignment, a line item, or a bridge between dim A and dim B. If two truths fit in one table, split them. Ambiguous grain is how double counting enters through the side door while the star schema diagram still looks clean.
2. Promote bridge keys to first-class columns
Surrogate or business bridge keys should be documented, tested for uniqueness at the intended grain, and used consistently in the . Do not leave bridges as "the join we do in that one view." First-class keys survive handoffs.
3. Share matching logic across fact tables
If source keys resolve to a dim with a shared module for fact A, fact B uses the same module. Divergent matching guarantees divergent headlines. Shared logic is cheaper than reconciliation meetings. Figure 4 is that ownership split.
Figure 4. One matching module for both fact tables. Publish its version with the remodel so headline metrics cannot drift a week later. Role-play FKs share types with gold.
4. Align FK types for role-playing dims
Every role of a role-playing dim needs matching types on every relationship endpoint. An int64 key next to a string role-play is a blank visual waiting for a demo. Fix gold and TMDL together.
5. Validate bridges before pages
Acceptance tests: bridge uniqueness, orphan rates, reconciliation between dim A-only and dim B-only slices on fixture keys. Do not accept "the page looks right" as grain proof. Visuals can look right while counting the wrong entity twice.
6. Sequence work as remodel, then visuals
When stakeholders ask for a prettier page, check whether the fact still lies. Remodel tickets unblock report tickets. Mixing them produces beautiful wrongness.
Why "helpful" gold views fail under filters
A convenience view that duplicates dim B attributes onto every dim A line will look fine with no filters. Add a slicer on each side together and cardinality explodes or collapses depending on relationship direction. The semantic model cannot invent a bridge you refused to store. If product owners want both "dim B rows per dim A key" and "dim A rows per dim B key" as first-class questions, they have already asked for a bridge. Your job is to stop pretending a flat view answers both without a grain statement. Figure 5 is the contrast.
Figure 5. Flat convenience views hide many-to-many until dual slicers disagree. An explicit bridge stores the grain so both directions can reconcile.
A minimal remodel checklist
List current consumers of the convenience view. Define the bridge grain in one sentence. Add keys and tests. Point the semantic model at the new fact or bridge. Rebuild affected pages. Only then delete or freeze the convenience view. Freezing without a delete date guarantees a zombie path. Publish the matching module version next to the remodel so the other fact tables cannot drift a week later.
Semantic tests that catch remodel regressions
Keep a tiny fixture pack: one dim A key with two dim B keys, one dim B key with two dim A keys if the business allows it, one unmatched dim A key, one unmatched dim B key. Assert counts from both directions after each gold change. Add a role-playing date check so date role A and date role B do not silently share a wrong type. These tests are dull. They are also how you stop rediscovering the same bug after every "small" enrichment tweak.
Failure modes
Convenience gold views. Many-to-many flattened until it fails.
DAX bridges. Business rules hidden in measures nobody owns.
Different matching per fact table. Headline metrics argue forever.
Mixed FK types on role-plays. Blank visuals blamed on "Power BI being weird."
Page-first delivery. Polish locks in the wrong grain.
No orphan metrics. Silent drops of unmatched keys on either side.
What to copy / what not to copy
Copy
- Many-to-many between two dims modeled with an explicit bridge or fact grain statement.
- Bridge keys validated (uniqueness, orphans) in gold pipelines.
- Matching and enrichment logic shared across fact tables.
- Role-playing relationship endpoints that share types with gold FKs.
- Report work on affected pages that waits on remodel acceptance tests.
- Convenience views that hide grain, deprecated with a removal date.
Do not copy blindly
Explicit bridges add tables and teaching cost. Hidden many-to-many adds trust cost. Shared matching modules require package discipline across notebooks. Remodel delays visible UI progress; it prevents rework of that UI. Strict orphan reporting can surface uncomfortable source gaps. Surface them; do not hide them in inner joins.
Primary references: Power BI relationships and star schema guidance on Microsoft Learn, plus Fabric gold-layer modeling practice. Pair diagrams with the grain sentence in the repo.
Bridge keys first. Shared matching next. Types aligned. Then polish the page. Reconciliation stops being a weekly mystery when grain is honest.