MIP Logo

How and why to avoid many-to-many relationship in Power BI

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

This blog aims to help those who are struggling to avoid many-to-many relationships in Power BI. Although Power BI supports many-to-many relationships, they are often avoided or used with caution because they can lead to unexpected outcomes. A many-to-many relationship occurs when there are multiple records in one table related to multiple records in another table. In this scenario, Power BI may struggle to determine how to aggregate data, which can result in calculation errors especially when a complex data model and calculations are involved.

A fact table with unique values can help avoid issues caused by many-to-many relationships because it serves as a bridge between two or more related tables. It enables one-to-many relationships between multiple tables, simplifying the structure of your data model.

 

How to create a fact table with unique values?

If you have hundreds of unique values that you want to put into the fact table, manual input might not be a good idea. It’s more efficient to start with a dataset that contains all the unique values you need, even if there are multiple records for each unique value.

Step 1:

Open the Power Query Editor in Power BI. Right click on any blank area in the query pane, hover over New Query, and select Blank Query.

Step 2:

Right click on your new query and select Advance Editor.

Step 3:

Use the following formular to extract all the unique values from a column in your existing table. Source is the table where you want to extract your unique values from. CountryColumn is the column storing values in the table, and ‘adm-0-name’ is the column from which you want to extract unique values. ‘UniqueCountries’ stores all the unique values extracted from the ‘adm-0-name’ column in the ‘World Dengue (2)’ table. You can adjust the names of each part of the formula to better suit your analysis.

Now, the new query is stored as a table in Power BI. It will contain only one column, which includes all the unique country names that you need. Once you have a table like this, you can connect it to other tables as needed.

Share this post