How To Create And Manage Relationship Between Tables In Power BI?

Hey guys, I’m back with a new concept of Power BI and I hope you will like it. Firstly, I would like to thank all of you guys for encouraging me to write these kinds of blogs. I have already discussed Power BI in my previous blogs. There, I have discussed with you that How to work with DAX functions and How to use DATATABLE function of DAX for creating static table in Power Bi. I know everyone has questions that why I’m focusing on Power BI topics and the answer is Power BI is a very good and famous tool for creating reports and Dashboards in predicting future analysis also. The main reason for writing blogs on Power BI is that it’s easy and quick to generate report and dashboard based on row data and publish it to the web service so that other users can also view it globally.

Now, today’s topic is very helpful while creating Power BI reports “Create and Manage Relationships between tables in Power BI”. There are some questions that what is the relationship in Power BI and why we need it. So, below are the answers to these questions. Let’s start.

What Us the Relationship in Power BI?

Let’s take an example of real life. In real life, relationship is a bonding between two or many persons that share their resources. Without having relationship, they don’t share their resources. Similarly, in Power BI relationships means the connection between two or many tables that share their columns with each other and based on these relationships we can extract the data from other tables as required.

Why We Need Relationship in Power BI?

Sometimes we need to fetch data from one table based on another table in that case if we don’t have the relationship between these tables then we can’t fetch the data and if we have already created the relation between them then we can easily fetch the required data. And also, based on the relationship we can set the filters on the data. So, it is best to make a relation between the desired table. A relationship between tables helps us to generate best insights based on the data. Sometimes Power BI itself detects the relationship between tables or generate the possible relationship.

One of the best features of Power BI is an Auto-detect relationship. This feature makes work easy for us.

Auto-Detect During Data loading

In power BI while loading data from different sources it automatically detects or creates relationship between two or more tables. During this phase, it also sets Cardinality, Cross filter direction, and Active properties. Power BI checks the column names of every table to find out any potential relationship existence that makes difference if these kinds of relation exist then it automatically creates those relations and if not then it does not create any kind of relation between any tables. Below are the screenshots of automatically or manually creation of relationships in power BI.

Create a Relationship by Using Auto-Detect feature

First, load the data from your desired source. After loading the data you can see the option Manage Relationships in the toolbar area in Power BI. Below is the first screenshot of this option.

Once you click the manage relationship button you can see that the pop-up window has been opened. In that window, you can see the relationships that exist in the tables, if not then you have to click on the auto-detect option below in the pop-up window. Below is the second screenshot.

You can see in the above screenshot, there is an auto-detect option. Just click on that button, after clicking the button you can see the screen where you can find if there is extra relation exist.

In the above picture, you can see that there is no extra relationship exist.

Create Relationship Manually

In this approach, you have to again click on the manage relationship button after clicking on that button you can see the pop-up window with new, auto-detect, edit and delete option at the bottom of the window. Below is the screenshot of the same.

As you can see the highlighted area is the option. In these options, you need to select the new option to create a new relation. Below is the screenshot of the next window after clicking on the new option button.

After selecting the tables you can see the preview as shown below.

In the above screens, you can see both tables and there is highlighted column on both the tables named “id”. These are the columns between which we want to create a relationship. The relationship only exists when there are unique values in either of the tables. If not then we can’t create a relationship between these two tables. By default, Power BI set the cardinality, cross-filter direction properties. But if you want to change these values you can do it later also.

Edit or Manage Existing Relationship

This is the option where you can edit the existing relationship. There are three additional properties you can play with these. Below are these additional properties.

Cardinality

Many-to-one ( *:1)

It implies that many columns in one table are connected to a column in other table and vice-versa.

One-to-one (1:1)

It implies that a single column of one table is connected with the single column of another table.

Conclusion

In this article, I have shared the deep knowledge on how to create and manage relationships between two table. I hope you guys have understood all the concepts of the same. If you have any query related to this topic or any other please feel free to contact us. Keep learning.

Leave a Comment