Power BI Relationships Explained

Power BI uses the concept of relationships to combine what would otherwise be separate tables or datasets. It’s very common to have different parts of a dataset come from different sources. To build a report you have to import all of the data and most often it’s the use of relationships that tells Power BI how the different pieces of data should work together.

Power BI relationships are an alternative to merging tables. They allow you to create and manage complex data models within your semantic model.

Relationships are a key concept in Power BI and data modeling in general. As you work with more datasets you’ll find that all of the information that you need to build a report is rarely all in one place. Even within a single database you may have a table for sales, and separate tables for item detail and customer information. We’ll explain how to use Power BI relationships to handle these situations, common data schemas and how to create or edit relationships in a semantic model.

Let’s dive in!

Understanding Power BI Relationships

A Power BI relationship is a connection established between two tables, indicating how data in one table relates to data in another. They’re similar to joining in SQL, merging in Power Query, or using a VLOOKUP in Excel with the benefit that they’re dynamic and easy to keep track of.

Any specific relationship between data in Power BI defines how different data points across tables are associated with each other. These associations enable users to perform cross-table analyses without needing to manually merge tables.

Without defining a relationships, measures may not calculate correctly, and filtering issues can occur when you click on a visual and a different visual on the same page, but based on a different data set won’t update as expected.

Relationships are the reason that visuals, measures and different report elements work together when they come from different sources.

Power BI and Star Schemas

Microsoft has a great write up that includes the image below explaining star schemas. They’re a cornerstone of modern data architecture and while you don’t explicitly have to use them in your own data modeling, it’s good to know that they exist because they’re so common.

The basic idea is that most of transactional sales detail is on a Fact table in the middle of the star which may have millions of rows of data. One transaction per row.

However, a customer might update their address and instead of having to update a large amount of records, the address information is saved in a separate smaller table, called a dimension table.

Illustration of a star schema in a data model from Microsoft
Understand star schema and the importance for Power BI | Microsoft Learn

Power BI can create a relationship between the sales fact table and the address dimension table using a common field, such as a customer ID number.

The common field between two tables is referred to as a key, and it’s similar to the column you would use to base a VLOOKUP off of in Excel.

Viewing Power BI Relationships with the Relationship Editor

To view or create new relationships in Power BI, click on the Model View button on the far left of Power BI Desktop. A diagram will appear showing existing tables and relationships. Lines connect data sources with arrows pointing in the direction of the relationship.

Mouse over one of the lines and the fields that are common between the two tables will be highlighted. The example below shows the “Trial Balance” table connected to the “Account Index” Table when the “Account Description” is the same between the two.

Example of 3 tables with different relationships established between them in the Power BI model view

Unless you’re building a report based on Azure Analysis Services or a pre-existing Power BI semantic model you’ll likely have to create your own relationships. At times Power BI will automatically detect relationships but it’s relatively unreliable with large datasets and typically requires primary keys between tables to be apparent which isn’t always the case.

Creating a Relationship Between Two Tables

To create a relationship between two tables in Power BI, click on the Model View button in Power BI Desktop. Then drag and drop the name of one field from one table to the corresponding field on another table within the data model diagram.

  1. Go to the “Model” view by selecting it from the left-hand side panel.
  2. Drag a field (typically a key column) from one table onto the corresponding field in another table.
  3. Adjust the relationship settings, such as cardinality and cross-filter direction, according to your data model’s requirements.

The screenshot below shows Account Description being clicked on and dragged and dropped onto another table.

Creating a new relationship in Power BI

Power BI will automatically create a line that automatically generates the connection and assigns a relationship direction.

The results of a newly created data model in Power BI

This process establishes a link through which Power BI can navigate from one table to another, enabling coherent data analysis across tables.

Each relationship has properties that can be adjusted in Power BI. When you click on a created relationship the Properties panel on the right side of the screen gives you the ability to further adjust the settings.

To delete a relationship in Power BI, click on the line connecting two tables in the Table View screen and press delete on your keyboard.

The Different Relationship Types in Power BI

Power BI employs different types of relationships to model how data interrelates across various tables in a dataset. Different relationship types can also be though of similar to SQL Joins or the way that data is filtered or duplicated when and if duplicate values occur within one of the related tables.

One-to-One (1:1)

A one-to-one relationship occurs when each record in the first table corresponds to one, and only one, record in the second table, and vice versa. This relationship type is less common in business scenarios but is useful for splitting a table for performance reasons or organizing distinct information that shares a unique identifier.

Use Case: Linking employee details where one table contains personal information and another contains contact details, with both tables sharing a unique employee ID.

One-to-Many (1:N)

The one-to-many relationship is the most prevalent in Power BI. It connects a single record from one table to multiple records in another. Typically, the table on the “one” side holds unique keys, while the table on the “many” side references these keys.

Use Case: Relating a table of products where each product is unique to a sales table where each product can appear in multiple sales records.

Many-to-One (N:1)

A many-to-one relationship is essentially the inverse of a one-to-many relationship, where multiple records in one table associate with a single record in another table. In practice, it’s treated similarly to a one-to-many relationship but considered from the perspective of the table on the “many” side.

Use Case: Connecting multiple order records to a single customer record, illustrating that a customer can place multiple orders.

Many-to-Many (N:N)

A many-to-many relationship allows multiple records in one table to relate to multiple records in another table. This type of relationship can complicate data models, typically results in an error when trying to create one and is generally resolved by splitting a table into multiple one-to-many relationships.

Use Case: Associating products with orders where each order can contain multiple products and each product can appear in multiple orders, necessitating an order line items table to manage these associations.

Power BI Relationship Cross-filter Direction

An important setting when creating relationships in Power BI is the cross-filter direction. This setting determines how filters applied to one table affect the related data in another table. It can be set to single (default) or both directions, influencing how data is filtered across the model.

For example, when creating a dashboard that shows sales by region, and you want sales to automatically be filtered by region when a report viewer clicks on a visual. If the semantic model is setup with relationships, you may have to define the direction of the cross-filter to ensure that the data is displayed appropriately on your report when a user interacts with it.

Power BI Relationships vs Merging

You might be wondering, why should you create relationships in Power BI instead of simply merging the tables together into one big flat table with all of your data. While it goes against the conventional wisdom it’s entirely possible to do this and achieve the same results.

In fact, there can be performance advantages to working with merged tables in Power Query compared to building relationships in Power BI. The biggest drawback is that if your relationships change or you’re working with a complex data model with many different sources it can be very difficult to go back and correct a merge.

Modifying a Data Model in PowerBI.com

It’s possible to edit a data model and change relationships using the Power BI Service. First, your admin has to enable the feature on a workspace, under workspace settings. Then when you edit the report you can open the data model allowing you to change relationships without having to open the Power BI Desktop app.

Screenshot of creating relationships using the Power BI Service at PowerBI.com

Microsoft continues to add features to Power BI and Fabric giving Power BI developers fewer reasons to use the Power BI Desktop app to make changes and build reports.

Conclusion

Power BI relationships are fundamental for building data models in the software. They are used to related tables of data that otherwise would not be connected. There is no limit to the number of tables you can add relationships between, but you should be aware of potential performance issues that may occur.

To avoid poor performance of a report for the end user you should consider merging tables using Power Query to combine them. It offloads some of the processing but can also be very difficult to manage changes or see how tables may or may not be related like you can with the diagram view.

Scroll to Top