Power BI many-to-many relationships: model them without wrong totals
Power BI many-to-many relationships describe data where one value in a table can match several values in another table, and the reverse. Customers with joint bank accounts are the classic case. Such data needs careful modeling, or the totals mislead. This guide explains the bridging table, the filter direction and the scenarios that Microsoft covers in its guidance.
- Bridging tables
- Filter direction
- Avoid fact-to-fact links
Last updated
What are Power BI many-to-many relationships?
In a one-to-many relationship, each value on one side matches a single value on the other. Real data often breaks that rule. For example, a customer can hold several accounts, and an account can belong to several customers.
Microsoft describes three scenarios. You may need to relate two dimension tables, such as customers and accounts. You may need to relate two fact tables. Or a fact table may store rows at a higher grain than the dimension, such as targets by year against dates.
Each scenario has its own design. So the first step is to recognize which one you face. The most common one, two related dimensions, has a clear and well-accepted solution.
Model Power BI many-to-many relationships with a bridge in four steps
These steps follow Microsoft's guidance for two dimension tables, such as customers and accounts.
Add both entities
First add each entity as its own table, and make sure that each one has a unique ID column.
Add a bridging table
Next add a third table that stores one row for each valid pair, for example each customer and account combination.
Create two relationships
Then create a one-to-many relationship from each entity table to the bridging table in Manage relationships.
Set one filter direction
Finally set one relationship to filter in both directions, so that filters reach the fact table. Then hide the bridging table.
Why do totals look wrong in Power BI many-to-many relationships?
Microsoft shows the problem with an example. A transaction table relates to accounts, and a visual groups the amounts by customer. At first, every customer shows the same balance as the grand total. So the filters do not flow all the way to the transactions.
The cause is the filter direction. The relationship between the account table and the bridging table propagates the wrong way. Once you set that direction to Both, each customer shows the correct balance.
One effect remains, and it is correct. The customer balances do not add up to the total, because a joint account counts for each of its holders. Microsoft calls such values non-additive. Therefore explain this to readers with a text box or a tooltip.
Three scenarios and the advice for each
Microsoft's guidance article treats these cases separately, and the advice differs for each one.
Two dimensions
Use a bridging table with two one-to-many relationships. Microsoft does not recommend relating such dimension tables directly.
Two fact tables
A direct link is possible but generally not recommended. Instead, add dimension tables and build a star schema.
Higher grain facts
A fact table can store rows at a higher grain than the dimension. Microsoft covers this case as its own scenario.
Should you relate two fact tables directly?
Power BI lets you set a relationship with many-to-many cardinality between two fact tables. Microsoft says this can be useful for quick and simple data exploration. However, it generally does not recommend the approach.
The main reason is flexibility. In Microsoft's example with orders and fulfillments, visuals can filter or group only by the order ID column of one table. Data quality is a second reason. If the data has integrity issues, some rows might be omitted during querying.
A star schema solves both problems. You add dimension tables, such as product and date, and relate them to each fact table with one-to-many relationships. Then readers can filter or group by any dimension column and summarize either fact table.
Modeling habits from Microsoft's guidance
Microsoft lists these points for related dimension tables, and they keep a model predictable.
- Give every entity table a unique ID column
- Store the valid pairs in a bridging table
- Set only one relationship to filter in both directions
- Hide the bridging table unless reports need it
- Hide ID columns that are not suitable for reporting
- Explain non-additive totals to readers in the report
Power BI many-to-many relationships: common questions
Do Power BI many-to-many relationships always need a bridging table?
Not always, but it is the recommended design for two dimension tables. Microsoft advises against relating such tables directly with many-to-many cardinality. A direct link implies that the related columns might hold duplicate values, and a dimension table should use its ID column as the one side of a relationship.
Why does each customer show the grand total?
Because the filter does not reach the fact table. In Microsoft's example, the relationship between the account table and the bridging table propagates in the wrong direction. Set the filter direction of that one relationship to Both. After that, each customer shows only the balance of their own accounts.
Should I hide the bridging table?
Usually, yes. Microsoft recommends hiding it unless it contains other columns or measures that reports need. The table exists for modeling, not for reporting. Also hide ID columns that do not suit reports. If an ID column stays visible, keep the one on the one side of the relationship.
Are many-to-many relationships in Power BI slower?
Microsoft's guidance does not give a general figure, so we will not claim one. It does note that filters applied to the one side of a relationship result in better filter performance. It also warns that direct links between fact tables limit how visuals can filter or group.
Can Power BI Agency fix a model with wrong totals?
Yes. One of our consultants traces how filters flow through your relationships, finds where they stop and corrects the design, often with a bridging table or a star schema. We work remotely in your own Microsoft tenant and hold weekly demos. Power BI Agency is a division of Sataware Technologies and is independent of Microsoft.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.