Power BI Relationship

Please read the previous article - Harnessing the Power of Data: A Beginner's Guide to Power BI to get a better understanding.

After creating few reports and working with my colleagues, I've noticed a significant gap in foundational Power BI data modeling content amidst the abundance of advanced topics like DAX functions and context transition. Taking the time to learn and master these foundational topics can greatly enhance the quality and effectiveness of visualizations, ultimately leading to more informed decision-making and valuable insights. It's important to strike a balance between the excitement of creating visualizations and the necessity of laying a solid foundation through proper data modeling techniques. Therefore, this article aims to address this void by elucidating the fundamental principles of relationships between tables.

What is the Relationship in Power BI?

A relationship refers to the connection established between different tables based on shared fields. It allows for the integration of data from multiple sources to analyze and visualize information effectively.

Now, Let’s go through the meaning of a relationship through an example. Let’s say you have a single table called Territories, and you want to analyze that in Power BI. Our Territories table looks like this:

In the Power BI report, if you want to build a visualization which is the Number of orders in each Continent, it would be like this:

As you can see, Territories can filter the Sales table to display the distinct count of OrderNumber for each continent. If I want to visualize the filtering, it would be like this:

What if we have more than one table?

Now let’s check what happens if we have more than one table. Let’s say the Return table is also a part of the solution. And We want Continent wise Return quantity with respect to each Product category.

As we don't have any active relationship between Continent and Returns table, we are getting Total return qty as 1828 (Fig. 1) for all the continent.

And, as we have relationship between Product and Returns table, we are getting correct Total order qty for all the CategoryName. But, when we bring Continent (Fig 2) into the picture, It is throwing 3 records for each Continent which is same as Total return qty with respect to CategoryName (Fig. 3).

Note:

In order to learn about relationships, I recommend turning off the auto-detect relationship feature (not permanently, but just for the purpose of learning). Relying on auto-detection can hinder understanding of what's happening behind the scenes. To turn off the auto-detect relationship feature in Power BI Desktop, you can follow these steps:

1. Open Power BI Desktop.
2. Navigate to the "File" menu at the top-left corner of the screen.
3. Click on "Options and settings," then select "Options."
4. In the Options dialog box, go to the "Data Load" section.
5. Under the "Data Load" section, find the "Data load" category.
6. Uncheck the box next to "Auto detect new relationships after data is loaded."
7. Click "OK" to apply the changes.

Why are we getting wrong numbers?

The reason behind the wrong numbers are due to improper defined relationship between tables.

Now, If we want to filter Total returns qty by Continent and CategoryName, the only way is to define a proper relationship between Territories, Returns and Products table.

Territories and Returns can be connected with each other by Territory key and Returns and Products can be connected with each other by Product key. Here, Territory is filtering Returns and Products is filtering Returns.

Relationships

When you want to query data from two tables, and the data of those two tables should be somehow connected, then you can create a relationship between them (if these two tables are not related through other tables).

To create a relationship between two tables, you need a variable that can link these two tables together. We call this variable a relationship field. You can simply drag that field from one of the tables (it doesn’t matter if it's from the source or destination), and drop it onto the corresponding field in the other table.

The relationship which is created might not be located in a way showing exactly from ProductKey to ProductKey. However, when you click on the relationship line, then you can see that fields that are part of the relationship are highlighted.

Fields can Have Different Names

The two fields from the two tables can have the same name or different names. If they have the same name (or let’s say similar name pattern), Power BI can automatically detect that relationship. The data type of the fields must be same whether it has same name or not.

Creating a relationship using the Manage Relationship

Under the Home tab in Power BI Desktop, you can click on Manage Relationship:

You will see the Manage Relationship window which will show all existing relationships and their from and to columns. You can create a new one by clicking on the New. You can also notice that there is a autodetect option right beside New, just in case you want to use. We can see the relationship that we have created in the previous step here.

Steps to create new relationship between two table:

Manage Relationships: Once your data is loaded into Power BI Desktop, navigate to the "Model" view by clicking on the "Model" icon in the left-hand sidebar. This view allows you to manage your data model, including relationships between tables.

Create Relationship: In the "Model" view, you'll see a diagram showing all the tables in your data model. To create a new relationship, drag the field from the primary table (the table containing the field you want to use as the basis for the relationship) and drop it onto the corresponding field in the related table (the table you want to connect).

Confirm Relationship: After dropping the field onto the related field, Power BI will automatically detect the relationship and draw a line between the two tables. You may need to confirm or adjust the relationship by double-clicking on the line. This opens the "Edit Relationship" dialog box, where you can specify the fields involved in the relationship and the type of relationship (e.g., one-to-many, many-to-one).

Configure Relationship Options: In the "Edit Relationship" dialog box, you can configure additional options such as cross-filter direction and whether to enforce referential integrity.

Save Relationship: Once you've configured the relationship according to your requirements, click "OK" to save it.

Review Relationship: Take a moment to review the relationship in the diagram to ensure it's correctly set up.

Test Relationship: Finally, you can test the relationship by creating visualizations or queries that involve fields from both tables. This allows you to verify that the relationship is functioning as expected and producing the desired results.

Creating relationships between tables is important. However, the cardinality will affect the process differently. Power BI is usually smart enough to understand the cardinality. However, cardinality itself is a big topic in relationships, which I will discuss later in another article.

Looking forward to reconnecting again in my upcoming article! If you have any questions or need assistance in the meantime, feel free to reach out. Until then take care!

For daily updates and new content, please follow me on Instagram - https://www.instagram.com/new_era_insightful_analytics/