What is Relationships in Power BI?
In Power BI, relationships refer to the way you connect and establish connections between different tables of data. These relationships are essential for creating meaningful visualizations, reports, and dashboards by allowing you to combine and analyze data from multiple tables coherently.
Table of contents
- Relationships are crucial for combining data from multiple tables in Power BI.
- There are four types of relationships: One-to-one, One-to-many, Many-to-one, and Many-to-many.
- You can create relationships manually or use Autodetect during data import.
- Managing relationships is essential for data model maintenance and analysis.
- Accurate data modeling and proper relationships are key to insightful Power BI reports and dashboards.
There are four types of relationships you can create in Power BI. They include:
- One-to-one relationships (1:1)
- Each record in one table is related to only one record in another table, and vice versa.
- One-to-many relationships (1:*)
- Each record in one table can be related to multiple records in another table, but each record in the second table is related to only one record in the first table.
- Many-to-one relationships (*:1)
- Multiple records in one table can be related to one record in another table, but each record in the first table is related to only one record in the second table.
- Many-to-many relationships (*:*)
- Records in both tables can be related to multiple records in the other table.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Create Relationships in Power BI?
There are two methods to create relationships in Power BI. They include:
- Manually creating relationships
- Use the Autodetect feature to create relationships
Method #1 – Manually
To create relationships manually, follow the steps outlined below:
Step 1: Navigate to the Manage relationships under the Modeling tab.
Step 2: Click on the New button in the Manage relationships screen.
This will open a Create relationship screen.
Step 3: Select the tables, cardinality (i.e., the type of relationship), and cross-filter direction.
Step 4: Click on the OK button once you have specified the required details.
Step 5: This will create a relationship between the tables as per your specifications.
Method #2 – Using AutoDetect
To create relationships using the Autodetect feature, follow the steps outlined below:
Step 1: Navigate to the Manage relationships under the Modeling tab.
Step 2: Click on the Autodetect button in the Manage relationships screen.
Step 3: Power BI will identify any relationships that exist between the tables and will display as per the screen highlighted below.
Step 4: Click on the Close button on the Autodetect screen. You will see the newly established relationship in the Manage relationships screen.
How to Manage and Edit relationships in Power BI?
You can manage and edit relationships using the Modeling tab.
Step 1: Navigate to the Manage relationships screen under the Modeling tab.
Step 2: You will be able to view all the active relationships that exist in Power BI.
Step 3: To edit an existing relationship, click on the Active checkbox next to an existing relationship and then click on the Edit button.
Step 4: Edit the relationship as per your requirements (For example, changing cardinality or cross filter direction or even the column) and then click on the OK button.
Step 5: You can also delete an existing relationship by choosing the relationship and then clicking on the Delete button.
Note that you can also manage and edit relationships in Power BI by navigating to the Model view tab.
Click on the left arrow, and you will see the relationship details in the Properties pane.
To amend any changes to the relationship, click on Open Relationship Editor. It will open the Edit relationship screen to apply any changes to the relationship. Post changes, click on the OK button to save the changes.
Note that, currently there is no native method or direct support to export relationships in Power BI. However, with advanced technical skills, you can manage export relationships in Power BI.
You can use relationships to build interactive Power BI reports and visualizations.
In this section, we will look at some of the examples of creating relationships in Power BI.
Example #1 – Configuring relationships with multiple tables
In this example, we will configure relationships with multiple tables in Power BI using the eSports Earnings dataset. The eSports Earnings dataset contains 2 tables.
- Highest_Earning_Players – Contains the details of individual players along with the total prize amount they have earned from eSports Tournament.
- Country Code – Contains the country code and the description of the code.
To configure the relationship between multiple tables, follow the steps below:
Step 1: Open Power BI Desktop and import the eSports Earnings dataset using the Get data option.
Step 2: Select all the underlying datasets available in the eSports Earnings file and then click on the Load button.
Step 3: Navigate to the Manage relationships available under the Modeling tab. You will view the available relationships for the underlying tables.
Step 4: Now to configure additional relationships between the tables, click on the New button. This will open a create relationship screen in a new window.
Step 5: Select tables and columns that are related. Define the Cardinality. In this case, the relationship between Highest Earning Players and Country Code will be many to many. Specify the Cross filter direction and then click on the OK button.
Step 6: The new relationship will now be available in the Manage relationships screen to be viewed.
Step 7: You can also view and edit the relationship between the tables by navigating to the Model view tab.
Step 8: Select the arrow or right-click on the arrow to view the relationship properties between the tables.
Example #2 – Establishing relationships when importing data in Power BI
In this example, we will try to establish a relationship when importing data into Power BI using the WLD_RTP_details_2023 dataset. WLD_RTP_details_2023 dataset has two different underlying datasets i.e.
- WLD_RTP_details_2023 – Contain the food prices estimates by each product and country for a period between 2007-23.
- ISO-Country Mapping – Contains the ISO country code to country description mapping.
To establish the relationship when importing data into Power BI, follow the steps below:
Step 1: Open Power BI Desktop and import the WLD_RTP_details_2023 file into the Power BI using the Get data option.
Step 2: Select all the underlying datasets and click on the Load button.
Step 3: Navigate to the Model view tab. Drag and drop the tables WLD_RTP_details_2023 and ISO-Country Mapping to the canvas.
Here we have defined the relationship between these tables using the Title column. The cardinality is a many-to-one relationship.
Step 4: Click on the Open relationship editor to further amend any other changes.
Step 5: Click on the OK button to save changes. Now your relationship between the tables is active.
In this example, we will go through creating a relationship between two tables using the Purchase_Details_Spend file to load into the Power BI.
Purchase_Details_Spend file contains
- Purchase_Details – contains the purchase details of customers along with the date and amount of purchase
- Spend_Category – contains the mapping between the Purchase category and the type of spend
To establish the relationship with these tables, follow the steps highlighted below:
Step 1: Load the Purchase_Details_Spend file into the Power BI Desktop using the Get data option.
Step 2: Navigate to the Manage relationships under the Modeling tab.
Step 3: Click on New and define the tables and columns related in the Create relationships screen.
Step 4: Once the relationship is established, you can view it in the Manage relationships screen as shown below.
Important Things to Note
- Ensure that the fields used in relationships have similar data types.
- Be cautious about the direction of relationships, especially in One-to-many or Many-to-one relationships.
- Maintain data integrity by keeping your data model clean and well-organized.
Frequently Asked Questions (FAQs)
• Active Relationship:
• This is the primary relationship that Power BI uses when you create visualizations.
• It’s the default relationship that is active when you build reports.
• Active relationships are typically used in most scenarios to create visuals and calculations.
• Inactive Relationship:
• An inactive relationship is an additional relationship that you can define between tables, but it is not used by default.
• You might use inactive relationships when you want to create alternative calculations or scenarios.
• Inactive relationships are particularly useful when you have multiple relationships between the same tables.
To delete all relationships in Power BI, follow the steps below:
• Open the Power BI Desktop application.
• Navigate to the Modeling tab and then click on Manage relationship
• Select all the Active relationships you want to delete by selecting the checkboxes and then click on the Delete button.
• Confirm the delete choice by selecting the Delete button.
• This will delete the relationship existing in Power BI.
Note: You can also delete the relationship by navigating to the Model view tab. Right-click on the arrow and choose the Delete option.
Relationships in Power BI might not work correctly for several reasons:
• Inaccurate or incomplete data can cause issues with relationships.
• Avoid circular relationships where tables are linked in a loop.
• If you have multiple relationships between tables, ensure that Power BI knows which one to use.
• Ensure that the relationship’s cardinality (one-to-one, one-to-many, many-to-one, or many-to-many) is correctly defined.
• There could be errors in table structure, relationships, and calculated columns or measures.
• Joins are used in the data preparation stage to combine data from multiple tables in Power Query Editor.
• Joins combine rows from different tables based on a common column, such as using SQL-like JOIN operations (e.g., Inner Join, Left Join, Right Join, Full Outer Join).
• Relationships are defined in the data modeling stage within Power BI Desktop.
• They specify how different tables are related to each other and enable you to create interactive visuals and calculations.
• Power BI uses relationships to create a context for your visuals and calculations based on the connected data.
This has been a guide to Relationships in Power BI. We learn to create and manage relationships in power bi, its types, methods and points to remember. You can learn more from the following articles –