Creating And Managing Relationships In Power BI

Introduction

In this article, we will learn about relationships, their definition, use, and how to create relationships in Power BI. After this, we will learn how to manage relationships in Power BI. Managing relationships means adding, editing, and deleting relationships as well as making these active or inactive.

Relationship Definition

As the name suggests, Relationship in Power BI is used to define the connections or the relation between two or more tables. The relationship is used when we want to perform an analysis based on multiple tables. Relationship helps us to display the data and correct information between multiple tables. It is used to calculate the accurate results also.

For this article, the data of DAX Excel sheet has been taken as data which is attached also for reference. To know more about how to use Excel as a data source in Power BI desktop, please have a look at my previous article.

• Implementing Power BI – Part 1

Need of Relationship

I have imported two Excel sheets for working on two tables in Power BI. When we try to display the data or the result of two or more tables without a relationship, then the following error occurs.

At first, I clicked on the Table option of VISUALIZATION pane. Then, I have dragged and put the values from both the tables. As we put some values of another table in Values pane, it gives an error with "See details" option. Once clicked on see details option, it gives an error as above. This error arrives because there is no relationship between both the tables.

Let’s now see the different ways of creating and managing a relationship.

Autodetect… option for creating relationships

Power BI has an inbuilt facility available to create the relationship on the best possible matches. For using this, the "Manage Relationship" option is available on the top ribbon of Home tab.

Home – Manage Relationships

Now, a window will appear with Autodetect… button as below. Click on Autodetect… button as shown below.

A message will appear which is the best available match for the relationship by Power BI between both the tables.



Now, click on "Close" and another window will appear with the Close option. Again, click the Close option.

Now, you can see the relationship of both the tables by clicking on VISUALIZATIONS relationship tab which is available in the left pane.

Drag and Drop option for creating relationships

By dragging one column and dropping it to another column, it makes the relationship between those tables based on those columns. This option is shown in the below image.

Manage Relationships New… option for Creating Relationships

We can create relationships between tables by clicking the New option of "Manage Relationships" tab and selecting each table’s column. Click on Home >> Manage Relationships >> New… 

Home – Manage Relationships – New…



Now, as per the above different methods, you have created relationships. You can perform different operations like edit or delete on this relationship.

You can use column values of both tables simultaneously anywhere you want. I have used it to display in a table as below.

Here, salary is the field of employee salary table while expense is the field of employee expense table. As we can see both are the different tables but we are able to display the data in a single table. This is not giving any error because we have already created a relationship among these tables.

Editing the relationships

Sometimes, it may require changing the relationship during your analysis. By following the below steps, you can change the relationship of tables.

By double clicking on relationships

By double clicking on the relationships line, it opens the same relationship window as you have seen after clicking on "Manage Relationships" option.

Here, you can change the relationships as you want.

Making relationship Active or Inactive

You can make a relationship active or inactive by just checking or un-checking the checkbox as below. Uncheck if you don’t want to make the relationship active.

Deleting the relationships

You can delete the relation by just right clicking on the relation and clicking on the Delete option as below.

Editing or deleting relationships by Manage Relationships option

We can edit and delete the relationships by clicking on Edit and Delete options as below which appear after clicking on "Manage Relationships" option of Home tab.

Click on Home – Manage Relationships

Summary

In this article, we have learned about the relationships in Power BI. We also learned what actually the relationships are and what its use is. We have also learned the different tips and techniques for managing the Power BI relationships. I have attached the sample files for your reference.

Hope you will learn and enjoy this article. You’re welcome to like, comment, share and any type of suggestion for my appreciation.