How to create relationships in Power BI
Your email has been sent
Understanding Power BI relationships and how to create them ensures your visuals filter and slice the way you expect. Learn more about Power BI relationships here.
If you’ve worked with multiple tables and had trouble with filters and slicers not producing the results you expect, it might not exactly be an error on your part. When combining data from multiple tables, Power BI relies on relationships between those tables. If there are no relationships or if you haven’t created the right relationship, your visual will most likely return an error. These errors can cause a lot of frustration and be difficult to fix.
In this tutorial, we’ll discuss what Power BI relationships are, how Power BI sometimes automatically creates them for you and how you can create them manually.
SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)
I’ll be demonstrating using Microsoft Power BI desktop on a Windows 10 64-bit system, but you can also use Power BI service for this task. To follow along, you can download the demonstration .pbix file here or work with your own data. This file contains three tables from Adventure Works Sales, which you can download from GitHub.
Jump to:
If you’re designing dashboards in Power BI and you’re not familiar with relationships, you’re at a disadvantage. Relationships between Power BI tables are important for pulling data together in meaningful ways for visuals. For instance, if order data and customer data are stored in separate tables — as they should be — you can’t reconcile an order to a specific customer without a relationship between the two tables.
SEE: How to create a field parameter in Power BI (TechRepublic)
Think of these relationships as you would your family relationships. You have a parent who may have other children. The relationship between you and this parent is a one-to-one relationship because there’s only one parent and one you in the equation. However, the relationship between your parent and all their children represents a one-to-many relationship.
There are three main types of relationships to consider when working in Power BI. Don’t worry too much about understanding these three types of relationships, because Power BI usually does a great job of taking care of relationships for users. However, a basic understanding will help for those times when you have to figure it out yourself.
Both tables can have only one matching record on either side of the relationship. Most one-to-one relationships are forced by business rules and don’t flow naturally from the data.
One table contains only one record that relates to none, one or many records in the related table. This relationship is similar to the one between you and a parent who has several other children.
Each record in both tables can relate to any number of records — or no records — in the other table. These tables require a third table, known as an associate table, to relate the other two.
The good news is that Power BI can interpret and create relationships when you import data. You can import any related data you like or work with the downloadable demonstration .xlsx file.
To import the demonstration tables, do the following:
Figure A
Figure B shows the tables in the Fields pane. Feel free to expand and review the fields or change the names if you want to. You won’t see any evidence of relationships here.
Figure B
If you’re not familiar with the data, I recommend reviewing each table in the Data window. Figure C shows the customer table. This gives you the opportunity to review the column names and data types so you can match them to other tables when considering relationships.
Figure C
When you load the data, Power BI attempts to find and create relationships. It does so by comparing column names for potential matches. If it can’t find matching columns, it doesn’t create a relationship. In our case, Power BI did create a relationship.
To view or create a relationship, click the Model tab. As you can see in Figure D, Power BI created a relationship between two tables: TableSales and TableSalesOrder.
Figure D
This happened automatically, but why? Power BI was able to create this relationship for two reasons:
To discover the type of relationship Power BI created, double-click the line between the two tables. The resulting window displays information about the relationship, as shown in Figure E. You can also edit the relationship. Notice that both tables have a column named SalesOrderLineKey.
Figure E
At the bottom, you can see that Power BI has created a one-to-one relationship based on the SalesOrderLineKey columns in both tables. That means there’s only one record in both tables where the SalesOrderLineKey value matches. Click OK and return to the Report window so we can base a simple visual on both tables.
To build the simple visual shown in Figure F, do the following:
Figure F
Without a relationship between the two tables, Power BI might let you build the visual, but it won’t display the data correctly. The many side — or the aggregate side of the visual — will display the same sum, average and so on for every record.
If you’re curious about these key fields, they’re not visible in visuals by default. You could add one to a visual, but in general, it won’t provide any meaningful information to the end user.
Rather, these values are helpful for identifying records uniquely. In relational database theory, they’re called primary and foreign keys. One side of the relationship must have a column of unique values. In a simpler dataset, you might use natural data, such as a customer name or a purchase order number to relate data.
After importing data and viewing the relationships Power BI has created, you might find that something’s missing. It’s possible that Power BI won’t detect all the relationships that should be shared between your datasets. When you think a relationship has been missed, use AutoDetect to find new relationships. This feature does exactly what it sounds like — it will try to detect new relationships.
Now, let’s run AutoDetect and see what happens:
As you can see in Figure G, this feature didn’t find any new relationships, but don’t let that deter you from using this feature with your own data.
Figure G
Click Close twice to return to the Report window. Sometimes you have to create a relationship manually.
When all else fails, you might have to create the relationship yourself. To do this, return to the Modeling window. As before, you see three tables and one relationship between two of them. We can’t create a relationship with TableCustomers, because there’s no common field with the other two tables. This is normal; some tables will have no relationships and some tables will have relationships with more than one table.
First, let’s delete the existing relationship and then recreate it. To delete, simply right-click the line connecting the two tables and choose Delete, as shown in Figure H. Then, click Yes to confirm the request.
Figure H
To recreate the relationship, click Manage Relationships and then click AutoDetect. This time, it finds and creates a relationship, as shown in Figure I.
Figure I
Click Close to confirm the relationship, as shown in Figure J. Then, click Close to return to the Modeling window.
Figure J
You can also create the relationship manually by dragging one field to another in the Modeling window. First, delete the relationship a second time and then do the following:
You can also use AutoDetect to create a relationship. To get started, delete the relationship a third time and then click AutoDetect. In the resulting dialog, do the following:
Figure K
When troubleshooting the relationship that supports a visual when it’s not working as expected, temporarily change the visual to a table. Problems are much easier to find in a table visual.
If Power BI fails to create any relationships, check system options as follows:
Figure L
The second option is unchecked by default. This option, when enabled, looks for changes in the data source relationships as you refresh the dataset. Power BI will update its relationships if the underlying relationships have changed. This option is great when needed but is best left disabled.
SEE: Hiring Kit: Database engineer (TechRepublic Premium)
At this point, you have a reasonable understanding of what relationships between tables are and why they’re important in Power BI. You also know how to create them if Power BI fails to do so when data is imported.
Read next: Best business intelligence tools (TechRepublic)
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to create relationships in Power BI
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply