The default behavior of Power BI is to detect and create relationships between tables automatically whenever you load a new table. In simple reports, this usually works just fine but in more complex reports with many tables and relationships, automatic detection usually doesn’t do such a great job. This is why we recommend to turn off the auto-detect function.
This is how you can turn off the auto-detect function:
1. Open the File menu
2. Select Options and settings > Options
3. Deactivate “Autodetect new relationships after data is loaded”.
Note that this setting is under Current File > Data Load which means that it is specific to the file you are working with, so you need to change it for every report.
What does this setting do?
Let’s see what happens when we load data while the Autodetect new relationship function is activated.
As new tables are loaded, relationships are automatically created. In many cases, this works fine, but in more complex reports wrong relationships might be detected and, while you could, of course, delete these relationships, you might simply forget to check all relationships and show wrong results.
This is why it’s a good idea to have autodetect deactivated. Below is what the load looks like without the relationships being created automatically.
You can see that you have unrelated tables after the load and have to create the relationships manually. While this might look like more manual work, it will save you time in the long run because you know which relationships need to be created and won’t create any unnecessary or wrong relationships. Also, you can still trigger the autodetect manually by selecting Manage relationships > Autodetect.
You are invited to take a look at the article explaining the star schema if you want to learn more about data modeling.