Power BI Many-to-Many Relationships - A Practical Modelling Guide

If you've spent any time building Power BI models for real businesses, you've hit this wall. You've got two entities that don't fit neatly into a one-to-many relationship, and suddenly your totals are wrong, your filters aren't propagating, and your stakeholders are asking why Customer-91 shows the same balance as the grand total.

Many-to-many relationships are one of those things that seem straightforward in theory but trip up even experienced data modellers in practice. I've seen this pattern come up repeatedly across our consulting work with Australian organisations - bank accounts with joint holders, students enrolled in multiple courses, employees assigned to multiple projects. The data relationship is obvious. Getting Power BI to handle it correctly takes a bit more thought.

The Classic Scenario - Why Your Totals Look Wrong

Let me walk through an example that mirrors something we dealt with for a financial services client. You've got bank customers and bank accounts. Customers can hold multiple accounts, and accounts can have multiple holders (joint accounts). Pretty standard stuff.

Your data model has three core tables. An Account table with account IDs and names. A Customer table with customer IDs and names. And a Transaction table recording money in and out of each account.

The problem is connecting customers to accounts. There's no single foreign key that works because the relationship is many-to-many. One customer relates to multiple accounts. One account relates to multiple customers.

The fix is a bridging table - sometimes called a junction table or an association table. This is a simple table with two columns: AccountID and CustomerID. One row for every customer-account association. If Customer-91 holds Account-01 and Account-02, that's two rows. If Account-01 is jointly held by Customer-91 and Customer-92, that creates another row.

You create one-to-many relationships from Account to the bridge table and from Customer to the bridge table. Hide the bridge table from report view since your users don't need to see it directly. Then add your Transaction table with a one-to-many relationship from Account.

This is where most people stop and assume it works. It doesn't, not yet.

The Bi-Directional Filter Problem

When you build a visual that groups transaction amounts by Account, everything looks perfect. Account-01 shows $75, Account-02 shows $200, the total is $275. Correct.

But group by Customer and things fall apart. Every customer shows $275 - the same as the grand total. That's clearly wrong.

The issue is filter propagation direction. When you filter by Customer, the filter flows from the Customer table through the bridge table to AccountID. But the relationship between Account and the bridge table only filters in one direction by default - from Account to bridge. The filter from Customer never reaches the Transaction table because it can't pass through the Account table in the right direction.

The fix: set the relationship between Account and AccountCustomer to filter in both directions. In Power BI Desktop, double-click the relationship line, and change the cross filter direction to "Both".

After this change, filtering by Customer-91 correctly shows $75 (only the transactions for accounts held by Customer-91), and Customer-92 shows $275 (transactions for both Account-01 and Account-02, since they hold both).

Why the Totals Still Seem "Wrong"

Here's where business users get confused, and honestly, where I've spent time explaining this to stakeholders more than once.

Customer-91 shows $75. Customer-92 shows $275. But the total shows $275, not $350 (75 + 275). That feels wrong intuitively.

It's actually correct. The total represents the sum across all customers without any customer filter applied - which is the sum of all transactions regardless of who holds the account. Since Account-01's transactions ($75) are counted for both Customer-91 and Customer-92, summing the individual customer balances double-counts those shared transactions.

This is what makes many-to-many measures non-additive. The individual row values don't sum to the total because the underlying data overlaps. You need to explain this to your report consumers or they'll file bug reports.

My honest advice: put a text box on the report explaining this behaviour. Something like "Individual customer balances may sum to more than the total because joint accounts are included in each customer's balance." Save yourself the support tickets.

Step-by-Step Implementation Guide

Here's the pattern we follow for every many-to-many relationship we build.

1. Create the bridge table. If your source system has an association table (many do), use that directly. If it doesn't, you'll need to build one in Power Query. The table should have just the foreign key columns from both dimensions - nothing else.

2. Create the dimension tables. Each entity (Customer, Account, etc.) needs its own table with a unique ID column. This is standard star schema design, but I'm mentioning it because I've seen people try to skip this step and build many-to-many relationships directly between dimension tables. Don't.

3. Build the relationships. Two one-to-many relationships, both pointing from the dimension tables to the bridge table. The bridge table is always on the "many" side.

4. Set one relationship to bi-directional. This is the one people forget. You need exactly one of the two relationships to filter in both directions so that filters can propagate from one dimension through the bridge to the fact table. Set the relationship that sits between your primary filtering dimension and the bridge table to "Both" for cross filter direction.

5. Hide the bridge table. Your report users don't need to see it. It's a modelling construct, not a reporting table.

6. Hide "many" side ID columns. If you leave ID columns visible, make sure they're on the "one" side of the relationship. Filters on the "one" side perform better.

7. Set nullable properties. If your bridge table shouldn't have null values in the ID columns, disable the Is Nullable property. This means data refresh will fail if there are missing values, which is actually what you want - it catches data quality issues early rather than letting bad data silently corrupt your reports.

When to Use Many-to-Many vs. Other Approaches

Not every complex relationship needs the many-to-many bridge pattern. Here's how I think about it.

Use the bridge table pattern when you genuinely have a many-to-many relationship between two business entities that both need to be used as independent filters. Customers and accounts. Students and courses. Products and suppliers.

Consider a role-playing dimension when one entity participates in multiple relationships with the same fact table. Order dates and ship dates are a classic example - you don't need many-to-many here, you need two relationships to the same date table.

Think about relating fact tables directly when you need quick exploratory analysis between two measures that share a common dimension. This can work for ad-hoc analysis, but I'd avoid it for production reports. Direct fact-to-fact relationships tend to produce confusing results and are harder to maintain.

Relating Fact Tables - When It Makes Sense and When It Doesn't

Power BI does let you create many-to-many cardinality relationships directly between two tables. This is different from the bridge table pattern.

We've used this in a few situations. Say you have an Order table and a Fulfilment table, and orders can be fulfilled in multiple shipments while fulfilments can cover multiple orders. You could create a many-to-many relationship directly between them.

It works for exploration. But the results are often unexpected. Aggregations might produce values that don't match what users expect because the filtering semantics of many-to-many relationships are different from one-to-many. Both sides of the relationship produce filter context, and the interaction between them can be hard to predict without thinking it through carefully.

For production models, I generally recommend using an intermediate fact table (like an OrderFulfilment table) and breaking it back into one-to-many relationships. More tables, yes. But much more predictable behaviour.

Higher-Grain Fact Tables

There's a third many-to-many scenario that catches people off guard. Your fact table stores data at a higher grain than your dimension table. For example, your sales targets are set at the monthly level, but your date dimension has daily rows. Or your budget is at the product category level, but your product dimension has individual SKUs.

This creates an implicit many-to-many relationship. Multiple date rows relate to one target row. Multiple product rows relate to one budget row.

The solution is usually to create a separate dimension table at the same grain as the higher-grain fact table. A MonthYear table that your targets relate to, connected to your main Date dimension through a separate relationship. This keeps the grain consistent within each relationship and avoids the ambiguity of trying to spread a monthly target across daily rows.

What We've Learned Across Projects

After building these models for organisations across financial services, retail, and professional services, a few lessons stand out.

First, always validate your totals. Build a simple test visual that shows the individual dimension values and the grand total. If the individual values sum to the total, great - your relationship is one-to-many and everything is additive. If they don't, make sure you understand why and can explain it.

Second, bi-directional relationships have a performance cost. Every bi-directional relationship increases the number of filter propagation paths Power BI needs to evaluate. For small models this doesn't matter. For models with dozens of tables and millions of rows, it can slow things down noticeably. Only use bi-directional filtering where you actually need it.

Third, document your bridge tables. Six months from now, someone will look at your model and wonder why there's a hidden table with two ID columns. Leave a description on the table explaining what it bridges and why.

If your organisation is working with Power BI and running into data modelling challenges like these, our Power BI consulting team can help. We work with businesses across Australia to build models that handle real-world complexity without falling over. And if you're looking at broader business intelligence strategy, our BI solutions practice can help you think through the architecture from the ground up.

For the full technical reference on many-to-many relationships, see Microsoft's guidance on many-to-many relationships.