Have you ever found yourself in a situation where you have to create a dashboard where data lies in multiple datasets? If you have, you are not alone. Most visualization tools (including Power BI) introduced this feature of “relationships”, which allows you to connect datasets together. This article will explain the types of relationships you can use in Power BI, and how to effectively use them in your dashboards.
How do Relationships work?
Relationships work by joining datasets together with a specific key. This key is usually an attribute like department name, gender, or product code. It is important to note that the key MUST BE found in both datasets to have the two datasets mapped.
After you’ve understood how relationships work, the next important thing you need to know is the type of relationship, as different types of relationship will lead to a different output from your dataset.
Types of Relationships
There are 3 possible relationships that can be applied, and they are:
- One-To-One Relationship
- One-To-Many Relationship
- Many-To-Many Relationship
We will go into detail and explain what each of them means. For easier reference, we will be referring to the two datasets as dataset #1 & dataset #2, based on the order of the relationship above. (i.e. One – To Many Relationship would mean dataset #1 is One, and dataset #2 is Many)
One-To-One Relationship
The one-to-one relationship means that the records in Dataset #1 will only associate with Dataset #2 once maximum. This is because as the specific keys in both datasets are unique, Power BI will only be able to find the respective unique key once in each dataset.
In the example below, the Orders & Returns table is joined with Order ID as the key. This resulted in an output where the Returns Status column is together with the Orders table.
One-To-Many Relationship
The one-to-many relationship means that the records in Dataset #1 will be associated with multiple entries in Dataset #2, but Dataset #2 will associate itself with only one entry in Dataset #1. This is because the specific key in dataset #1 is unique, but not unique in dataset #2. Power BI will then match the unique key based on the number of occurrences of the key in dataset #2.
In the example below, the Orders Table & Returns table are joined using Order ID as the key. Because there is 2 Order ID: 90192 in the Product table, the Joined table output will result in two rows, as one row will be for product ID 4039423, and the other for 4204532
BONUS: datasets with no unique key are 99% of the time, factual tables. Factual tables usually contain data that will be used for analysis.
Many-To-Many Relationship
The many-to-many relationship means that both datasets have a common column, but are not unique. This means that Dataset #1 can be associated with multiple entries in Dataset #2, and vice versa. Power BI will then match the common key in each dataset to create the joined dataset. Many-to-many relationships expand the dataset by a significant amount, as it combines. Many-to-many relationships are SELDOM-USED & should only be used when you are aware of what it does and have a valid reason to do so as well. I highly recommend relooking at your dataset if Power BI suggests you use a many-to-many relationship. The example below explains why.
With a many-to-many relationship mapping on Product ID, we will notice that Customer ID 3 will be mapped to both Order ID 4345394 and 4345493, which might not be the case. This is a good example of the potential data error when using many-to-many relationship mapping.
How to create Relationships in Power BI
In the example below, I have two datasets, the Orders and Returns table. The Orders table shows me the orders made by customers, while the Returns table shows me which orders have been raised for a return request. I would want to identify the number of orders that have been issued a return. As the datasets are separate, we will use the power of relationships to help us derive this insight.
Steps:
Step #1: Upload the Orders and Returns sheet from SuperStoreUS-2015 Excel sheet
Step #2: Click on the Relationships tab on the left
Step #3: Under the ribbon, click on Manage relationships
Step #4: Click on “New”
Step #5: Select the datasets in the drop-downs
Step #6: Select the column that will be used to join the datasets together (in this example, it will be Order ID)
Step #7: Select the Relationship type used to join the two datasets together (in this example, it will be many-to-one), and hit ok.
Step #8: You’ve successfully joined the two tables together using Relationships in Power BI
Final Thoughts
Relationships in Power BI can be a little tricky and dangerous if you do not know what you are doing, especially Connecting your data in a many-to-many relationship without a rationale reason. However, it is useful knowledge to have, as there will be instances where data points are only found in separate datasets and you to use them in a single dashboard. As a data analyst, I highly recommend understanding Relationships and how they work so that you can plan and design your dashboards more effectively. To find out more about Relationships in Power BI, you can also refer to Microsoft’s learn platform