Published on August 11th, 2015 | by Fredrik Knalstad0
Auto create relationships in Power BI Desktop, YES or NO?
Power BI Desktop has become my best friend over the latest weeks. It’s packed with features to make it easy for analysts to actually concentrate on the analysis part, rather than using most of the time trying to collect the data and build modulation between then.
By default Power BI Desktop comes with a new feature called Autodetect relationships. When you import more than two tables into the data model, PBD will try to create a relationship between the tables automatically.
Cardinality, Cross filter direction, and Active properties are automatically set, it will also look at column names to find any possible relationship.
When the data has been loaded into the data model and relationships have bee created, you can manually change the relationships by selecting the [Manage Relationships] in the menu.
In the Manage Relationships you can easy add, change or delete relationships in an easy way.
Working with many Tables
So we agree that this new feature is exciting and is really helpful. When working with many tables the autodetect feature can find more than one possible relationship between tables. There will only be one active relationship that will be set as default, but the others will be visible in the relationship view in Power BI Desktop. Sometimes the feature will detect relationships that are wrong, and not needed.
This can easily be handled in “Manage Relationship” and deleted there. But sometimes it’s easier to create the relationships manually and not use the autodetect feature.
Turn of Autodetect Feature
Click on “File” -> “Options and settings”
In the “Options and settings”, select [Options].
In the Options menu select “Data Load”. In the relationships section there are two options.
Import relationships from data sources
Before the data is loaded to the model, Power BI Desktop will try to find existing relationships in the tables that are about to be imported. Example is using foreign keys in a relational database (SQL).
Autodetect new relationships after data is loaded
After the data is loaded the autodetect feature will try to find other possible relationships in tables that may have a relationship between them, then create the relationship. When the refresh button is clicked, new possible relationships will be created during the load.
When both of the options are not selected, the relationships need to be created manually.
The configuration follows the pbix file, so when new files are created the default configuration is loaded.