Beginner’s Guide to Relationships in Power BI

Are you new to Power BI and confused about what relationships are and how they work?

Relationships in Power BI are very powerful when configured correctly and can give you the exact information you’re looking to extract quickly and efficiently. It can also cause frustration when it’s not working properly, so it is important to properly understand how it works and how to set it up correctly.

Fortunately, the concept of relationships is rather simple once you get a hang of the fundamentals of how to configure your tables.

You will learn:

1. Why you should use relationships in Power BI
2. How to create relationships
3. What is cardinality and how to configure them
4. Configuring relationships with multiple tables
5. Explain Single/Both Direction setting
6. How to troubleshoot relationships

Full video tutorial

Watch the tutorial in the video, or you can scroll below to keep on reading and watch only the relevant sections of the video:

1. Why you need to establish relationships

Relationships are useful to get related table from one table to another without having to create a huge merged table with all the columns, also called Flat Tables.

Flat tables are a common practice in Excel, but not so great in Power BI, as I explain in this tutorial.

If you don’t create relationships between tables, you will see that the same number will get repeated for each row. Let me show you in the example below:

2. How to create relationships

There are different ways to create relationships in Power BI: Autodetect or Manually

2.1 Create relationships using Autodetect

Auto-Detect is a useful feature especially for beginners, because it tends to get it right
quite often.

To get Auto-Detect working, go to Modeling Tab (1) in the ribbon and press Manage relationships (2) and use the
Autodetect (3) function.

Once you’ve done this Power BI will create the relationship for you.

If you return to the relationship view, you can now see that a relationship has been created as the two tables are connected to one another.

💡 If you create relationships using autodetect, make sure you always check the results in the Relationship View as Power BI sometimes will get it wrong. 💡

Curbal Power Tip #1

Let me show you:

2.2 Set the relationship manually

If Autodetect did not do it properly you can always set the relationship manually.

To set the relationship manually, you simply drag the common field, the column that exists on both tables, from one table to the other one.

💡 Both columns need to have unique values or the relationship will be automatically set to Many-to-Many. Many to many relationships should be avoided as if they are not used properly, they can give wrong results in the calculations. 💡

Curbal Power Tip #2

Let me show you how to do it in the video:

3. What is cardinality, direction and how to configure them

Cardinality is term used in data modeling that indicates how many distinct values are in a column that is involved in a relationship.

There are three types of cardinality:
1. One-to-one: where both columns have unique values
1. One-to-Many/ Many-to-one: where only one column have unique values
1. Many-to-many: where both columns do NOT have unique values

The cardinality is symbolized in the data model with a 1 for the one, and a * for the many side.

Let me show you with an example:

4. Configuring relationships with multiple tables

One of the most powerful features Power BI has, it to connect to more than table through relationships.

In order to properly explain how and what cross filtering direction is, we need to create a little bit more complex model and to do that we are going to add a third table to our model and join it with relationships.

5. Explain Single/Both Direction setting

To the right of the cardinality options in the relationship pane, we see the Cross Filtering Direction. Here we have the options to choose either Single or Both.

In the relationship line connecting the tables, you will see one or two arrows pointing either to both directions or single one direction. These indicate whether the Cross Filtering Direction is Single or Both.

💡 Many to many relationships (or relationships set to both) should be avoided as if they are not used properly, they can give wrong results in the calculations. 💡

Curbal Power Tip #2

The best way to explain cross filtering direction is with an example, so you can see what it does:

6. How to troubleshoot relationships

Here you have some tips to troubleshoot relationships:
1. Avoid the Both direction setting
2. Avoid setting all relationships to both as it creates ambiguity
3. Use a star schema as often as possible
4. If you are a beginner, you can have power bi create relationships for you by default. Check them though!!