Relationships are the heart of Power BI modeling, and Power BI Desktop has a way to help and identify the relationships based on the source, or the data. However, often, this ability to automatically detect a relationship is more troublesome than helping, especially when you deal with tables that are not designed for reporting. In this short blog and video, I will explain what is the problem and how to fix it.
What is the auto-detect relationship?
If you have two tables, with common fields between them, Power BI will be likely to automatically create a relationship between the two tables when you load the data for the first time into Power BI.
This auto-detect relationship considers many things to create the relationship, things such as column names, table names, the values in each column, the cardinality of the values, having unique values in one of the tables, etc.
In a well-designed table diagram, this is often very helpful and creates all the relationships automatically for you, so you don’t need to do extra work.
However, in the cases that your table design is not well, you might have similar field names in different tables that have no conceptual relationship to each other. In those cases, the auto-detect relationship feature is more troublesome than helping. Below is an example;
When the auto-detect relationship messes up the model
If you add a new table to the model that has field names (and values) matching with other tables in the model, then Power BI automatically creates a relationship between each of the existing tables and the new table, and this can be sometimes too many new relationships.
How to fix it? Disable the auto-detect relationship in Power BI
To disable the auto-detect relationship feature in Power BI, you have to go to File>Options and Settings>Options.
You can then go to the Current File>Data Load;
The two options that I recommend unchecking are: “Update or delete relationships when refreshing data”, and “Autodetect new relationships after data is loaded”.
This means that, from now on, you have control of relationships, you have to create them whenever needed. If you like to learn more about why relationships are important read my blog article about Power BI relationships demystified.
Remember that this is just a recommendation. There are scenarios (if the table’s model design is already very well defined) that you can just leave the auto-detect relationship feature on, and that helps even to speed up the development of your model. But I do not recommend the auto-detect-relationship to be on for those who are at beginner modeling stages.