Power BI Design Tip: Disable the Auto-Detect Relationship

Power BI design tip, disable the auto-detect relationship

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.

auto-detect relationship in 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.

the relationship between the two tables detected correctly in Power BI

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.

auto-detect relationship works well with a good table design

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.

adding a new table automatically adds a lot of new connections

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;

disable the autodetect relationships in Power BI

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 Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
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.

Leave a Reply