Ben Richardson
The revised bar chart after the relationship has been created.

The Power BI Data Model: Working with Relationships

March 4, 2020 by

In this article, we look at how to create different types of relationships between two or more tables in the Power BI data model.

Anyone who has worked with a relational database management system knows that tables are related to each other via foreign key constraints. Table relationships are implemented to normalize the database, which is crucial to data integrity and avoids data duplication.

There are four main types of relationships between database tables: One to Many, Many to One, Many to Many, and One to One. A full explanation of these types is beyond the scope of this article. See Learn SQL: Types of relations article for a full explanation.

Creating a dummy dataset

In this section, a dummy dataset will be created in SQL Server. In the next section, you will see how to connect Power BI with SQL Server and then import the dummy dataset into Power BI. We will create a simple database for a fictional book store. The name of the database will be the BookStore.

For details of creating a dummy dataset, please refer to the ‘Creating A Dummy Database’ section of SQL JOIN TABLES: Working with Queries in SQL Server

Setting Autodetect Relationships to False

By default, depending upon the column names of the table, Power BI may assume relationships between different tables by default. We do not want that.

To disable this option, on Power BI Desktop, go to “File -> Options & Settings -> Options” as shown below:

Disabling automatic relationships in Power BI

From the dialogue box that appears, uncheck the option “Autodetect new relationships after data is loaded” option as shown in the following screenshot:

Editing setting in the Data Load options

Connecting Power BI with SQL Server

We have created a dummy dataset; the next step is to connect Power BI with SQL Server and then to import the BookStore dataset into Power BI. To see the detailed explanation for how to connect Power BI with SQL Server, have a look at this article.

Implementing Relationships in the Power BI Data Model

It is extremely easy to create relationships between two or more tables in a Power BI data model.

Let’s first see the effect on the visualization when there is no relationship between tables.

In the figure below, we plot a “Stacked Bar” chart where the x-axis contains the names from the Categories table and the bars correspond to the price of the book. Since there is no relationship between the Books and Categories columns currently in our Power BI data model the bars show the total price for all the books against all the category names. We want to show the price of books per category. To do so, we need to define a relationship between Books and Categories tables:

Bar chart in Power BI before a relationship is created.

To define a relationship, click the “Manage Relationships” option from the top menu as shown below:

Selecting the Manage Relationships tool

You should see the following window. Since there no relationship exists between the tables in the dataset, the window is empty:

The first window shown when you click manage relationships and there are no current relationships in Power BI.

Click the “New” button from the above window to create a new relationship.

In this window select the first table from the dropdown list at the top and then click on the column name on which you want to implement the relationship. In the figure below, we select “Categories” as the first table name and “Id” as the column name.

From the second dropdown list, we select the “Books” column and select “CategoryId” as the column used to implement a one-to-many relationship between “Categories” and “Books” table.

Finally, you need to specify the type of relationship via the cardinality dropdown list, which in our case will be “One to Many”. Look at the following figure for reference:

Creating a new relationship in Power BI

Once you click the “Ok” button, you will see that your bar stacked plot will be updated automatically to reflect the new relationship. Now you can see the price of books per category:

The revised bar chart after the relationship has been created.

Another way to implement a relationship between two entities in a Power BI data model is by going to the “Relationship” view, which is the third option in the vertical list of options on the left-hand side of Power BI. The option is highlighted in yellow in the following screenshot:

Selecting the relationship view in Power BI

Once you click the relationship view, you will see the following window:

Power BI showing the current relationships created.

You can see the new relationship between the “Books” and “Categories” table that we just created in our Power BI data model.

As is the case above, sometimes the relationship might not be located so that it is lined up precisely between two fields. If you are ever unsure and want to quickly check a relationship, remember when you click on the relationship line will then be able to see that fields that are part of the relationship as they will be highlighted in yellow.

To create a one-to-many relationship between the Authors and Books tables, click where “Id” shows below “Authors” and drag it to the “AuthorId” column of the books table. Once you do that, you should see a relationship between Authors and Books as well, as shown below:

View in Power BI relationship managed having created relationship between Authors and Books tables.

Conclusion

Relational databases contain relationships between multiple tables. Power BI also allows you to create similar relations in the Power BI data model. In this article, you saw how to implement relationships between multiple entities in the Power BI data model and how to quickly check that you have connected the correct fields.

Ben Richardson
8,860 Views