← All Kits · Power BI Kit

Many-to-Many Relationships in Power BI, and Why the Totals Go Strange

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

You drag two tables together, Power BI announces a many-to-many relationship, and you click through it. The visuals work. Then somebody notices that the region totals add to more than the company total.

What you do: add a bridge table. Many-to-many is almost always a sign that a table is missing from the model, not that you need a cleverer relationship setting.

The short version. Every relationship should have one side where the key appears exactly once.

What actually happens

Suppose Sales has a product_code that repeats, and ProductNotes also has product_code repeated because there is one row per note. Neither side is unique, so when a filter is applied Power BI cannot tell which rows on the far side it means.

The consequences are three, and they are the diagnosis:

That third one is worth knowing on sight. Power BI adds a blank member to a dimension when the fact table contains keys the dimension does not have, so a blank in a slicer is a referential integrity finding, exactly as in the data quality checks.

The bridge table

The fix is a small table of the distinct keys, related one-to-many in both directions:

Products = DISTINCT(UNION(
    SELECTCOLUMNS(Sales,        "product_code", Sales[product_code]),
    SELECTCOLUMNS(ProductNotes, "product_code", ProductNotes[product_code])
))

Then relate Products[product_code] to each of the other tables, one to many, filtering from Products outward. Now every relationship has a unique side, filters flow in one direction, and totals behave.

Better still, build the bridge upstream in the source or in Power Query rather than as a DAX table, so the model has one fewer moving part and the key list can be reviewed by a person.

The legitimate many-to-many cases

Some business relationships genuinely are many-to-many, and the bridge is still the answer:

In every one of these, a fact can legitimately count in two categories, so the categories will add to more than the total. That is not a bug, and it must be stated on the visual. A subtitle reading "customers can appear in more than one segment, so the segments sum to more than the total" prevents the entire conversation.

Where an allocation exists, use it: a payment split across two invoices should carry a percentage on the bridge, and the measure multiplies by it. Then the parts do sum to the whole, honestly.

Bidirectional filtering

Setting a relationship to filter both ways looks like a shortcut around the whole problem. It creates two new risks: ambiguous filter paths once a third table joins the model, and performance costs on large tables.

Prefer a one-way model plus a targeted measure where you genuinely need the reverse direction:

Customers with sales =
CALCULATE(
    DISTINCTCOUNT(Customers[customer_id]),
    CROSSFILTER(Sales[customer_id], Customers[customer_id], BOTH)
)

That turns a model-wide setting into a decision made in one measure, where it can be read and changed by whoever maintains it.

How to apply this to your own work

1. Open Model view and look at every relationship line. Any marked with an asterisk on both ends is a many-to-many.
2. For each, ask which table should own the key list. That table is your bridge, and it may already exist.
3. Check every slicer for a blank member. Each one is a key mismatch worth chasing upstream.
4. Test the totals: put the category breakdown and the grand total on one page and confirm they agree, or state why they do not.
5. Replace bidirectional relationships with CROSSFILTER inside the measures that need it.

The one habit to keep

Before creating any relationship, ask which side has the key exactly once. If the answer is neither, the model is missing a table, and adding it now is much cheaper than explaining the totals later.

Does the grand total on your main page equal the sum of the rows above it?