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.
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.