Power BI Many-to-Many Relationships Explained

Power BI guide Home / Guides / Power BI

Model many-to-many relationships in Power BI with bridge tables, controlled filter direction and total validation.

2026-06-16 10 min read min read Digital Adaption On this page

1. Understand the problem
2. Create a bridge table
3. Validate totals

Many-to-many relationships are where a lot of Power BI models become difficult to trust. They are sometimes necessary, but they need deliberate modelling rather than a quick relationship line between two messy tables. For broader reporting repair work, see the Power BI consultancy and ERP reporting analytics pages.

Practical answer

Use a bridge table with one row per shared key where possible, keep filter direction controlled, and validate totals against source extracts before trusting the visual.

1 Understand the problem

A many-to-many relationship means values in both tables can appear more than once. That can happen with customers and buying groups, products and categories, employees and sites, or invoices and projects.

Power BI can create many-to-many relationships directly, but the result is often harder to reason about. In commercial reporting, the safer pattern is usually a star schema with dimensions, facts and bridge tables.

• Identify the business grain of each table.
• Check whether duplicates are expected or caused by poor source data.
• Decide which filters should flow into which facts.
• Avoid bidirectional filtering unless you have a clear reason.

2 Create a bridge table

A bridge table contains the distinct keys that connect two sides of the model. It gives Power BI a controlled path for filtering and makes the model easier to explain.

If the relationship includes weighting, ownership percentage or effective dates, the bridge table should include those attributes rather than hiding them in a measure.

Customer Bridge =
DISTINCT (
    SELECTCOLUMNS (
        CustomerAssignments,
        "CustomerKey", CustomerAssignments[CustomerKey],
        "GroupKey", CustomerAssignments[GroupKey]
    )
)

3 Validate totals

The danger with many-to-many models is double counting. A customer attached to two groups may be counted twice unless the measure and relationship design match the reporting question.

Validate at the grain the business cares about. If the report is used for customer revenue, test customer totals first. If it is used for group performance, test group totals and explain any allocation rules.

1. Create a simple table visual with the key, base measure and related dimension.
2. Compare the grand total to a source-system export.
3. Filter one group or category and trace a sample transaction.
4. Document whether values are duplicated, allocated or deduplicated.

Common mistakes to avoid

Solving source duplicates in the relationship dialog

If the source data has accidental duplicates, fix or model the grain first. A many-to-many relationship can hide the underlying data problem.

Turning on both-direction filters everywhere

Bidirectional filters can create ambiguous paths and surprising totals. Use them sparingly and document why.

Skipping sample transaction checks

Grand totals can look right while individual rows are wrong. Trace real examples through the bridge.

Validation checklist

• Every table has a documented grain.
• Bridge table keys are unique where they should be unique.
• Filter direction is intentional and limited.
• Totals match the source or the allocation rule is documented.
• A sample transaction can be explained from source to visual.

Need the report to reconcile with ERP?

Digital Adaption helps UK SMEs rebuild trusted Power BI, ERP and operational reporting after migrations, ownership gaps and model drift.

Review reporting trust

FAQ

Is many-to-many always bad in Power BI?

No. It is sometimes the correct model. The issue is using it casually when a bridge table, cleaned dimension or clearer grain would be safer.

Why do my totals increase after adding a relationship?

The relationship may be duplicating rows through repeated keys. Check the grain of both tables and validate a single key before reviewing the whole report.

Start with a 30-minute data risk call