There are tons of articles about advanced tips of Power BI modeling such as advanced DAX, and context transition. However, based on my experience with our clients, I realized there is a big gap in basic content. Hence, this article is going to cover the basic essentials of the relationship. Tips such as: What is a relationship? Why do you need it? And what is happening logically behind the scene of a relationship? To learn more about Power BI, read Power BI book from Rookie to Rock Star.
What is the Relationship in Power BI?
Let’s go through the meaning of a relationship through an example; Let’s say you have a single table for Store, and you want to analyze that in Power BI. Our Store table looks like this:
In the Power BI report, if you want to build a visualization which is the count of stores in State, it would be like this:
As you can see, the State can filter the table to show the count of records for each state. If I want to visualize the filtering, it would be like this:
What if more than one table?
Now let’s check what happens if we have more than one table; Let’s say there is a Sale table also as part of the solution. Sales table have some information, such as how many of each book title, at which date, at what store is sold. Here is a screenshot of the data in the Sales table;
In order to learn relationships, I do recommend you to turn off the auto-detect relationship (I don’t mean always! Just for the purpose of this learning). Having the auto-detect relationship stops you from learning what is happening behind the scene. If you want to turn off this feature, you need to first go to options of Power BI Desktop;
And these are options to uncheck if you want to turn off the auto-detect relationship;
If we load the Sales table also in the same solution, now we will have two tables. Without any relationships (because the auto-detect relationship is turned off) as below;
Now, if we want to filter and find out what is the total Qty of book titles sold in each store, we can do that using a visual like below;
This would be very similar to the previous visualization on the Store table. The filtering happens all in one table, however, this time the table is Sales;
Now, let’s do a visualization like this: The total Qty of titles sold in each state:
As you can see in the above visualization, the total of qty for each state is 493! And the total qty in the Sales table is also 493! Obviously, this time filtering didn’t happen correctly. Because Sales only knows about the stor_id, not the state:
If we want to filter this table by store, the only field that gives us such ability is stor_id and that doesn’t know anything about the state.
On the other hand, the Store table has the state field, but knows nothing about the Sale and Qty! However, there is a stor_id field in this table too. Which is the unique id of the store per each row (store);
In this example, we need both tables, because they both contain fields that we need in our visualization: State field from Store table, and Qty field from the Sales table. The stor_id field is the field that can help us in looking up one value in the other table. Let’s see that through an example:
The store Barnum’s located in Tustin, in state CA. and the id of this store is 7066:
The Sales table on the other hand side, tell us how much sales we had in the store 7066 (which is Barnum’s store);
These two tables can be connected to each other, using stor_id. Or let’s say it in other words; these two tables can be RELATED to each other using the stor_id field.
Relationships
When you want to query data from two tables, and the data of those two tables should be somehow connected, then you can create a relationship between them (if these two tables are not related through other tables).
To create a relationship between two tables, you need a field that can link these two tables together. We call this field a relationship field. You can simply drag that field from one of the tables (doesn’t matter source or destination), and drop it on the same field on the other table.
After drag and drop, you should see the relationship created like below;
The relationship which is created might not be located in a way showing exactly from stor_id to stor_id. However, when you click on the relationship line, then you can see that fields that are part of the relationship are highlighted.
Fields can Have Different Names
The two fields from the two tables can have the same name or different names. If they have the same name (or let’s say similar name pattern), Power BI can automatically detect that relationship. Remember that we turned off that feature earlier in this article to make the learning process easier. By default, the auto-detect relationship is on and would have detected a scenario like this that we had stor_id in both fields and would have created the relationship.
If field names are different, then you have to create the relationship manually. Like what we have done in above. Alternatively, there is another way to create the relationship too.
Creating a relationship using the Manage Relationship Menu Option
Under the Home tab in Power BI Desktop, you can click on Manage Relationship;
You will see the Manage Relationship window which will show all existing relationships and their “from” and “to” columns. You can create a new one by clicking on the New. You can also notice that there is a “autodetect” option right beside “New”, just in case you want to use.
We cannot see the relationship that I have created in the previous step here, because I removed it manually to create it again this way and show you this as the second method of creating it.
After clicking on New relationship, then in the Create Relationship window, you will be able to set the source table, the destination table, and you have to also click on the relationship field to highlight it in each table. Like what illustrated in the below image:
If you choose the Stores table as the source or Sales table as the source makes no difference, however, the cardinality will make the process different. Power BI usually is smart enough to understand the cardinality. However, Cardinality itself is a big topic in the relationship, which I will talk about it later in another article.
There are many other configurations in the Create Relationship windows and each of those needs a full article discussion for themselves. In this article, we are only focusing on the basics of relationship. After clicking on OK with the configuration above, you should achieve the same relationship diagram as we did in the previous step:
Edit Relationship
In case you want to edit a relationship, you can either go through the Manage Relationship section under the Home tab, find that relationship, and Edit it;
Or you can just double click on the relationship line between the two tables,
Data Types Should Match
The two fields that you are connecting to each other, should have the same data type, or a data type that can be matched to each other (for example, text to number), otherwise, when you create the relationship, you get an error.
Linking Field Should Exists in Both Tables
If you don’t have the link field in one of the tables, then obviously you cannot create a relationship between them. In the above example, the stor_id is our link field and it HAS TO exists in both tables.
Relationship Means Filtering Across Tables
The whole purpose of creating relationships in Power BI is to be able to filter data by the fields from two separate tables. Creating a relationship like the above example means connecting the two tables like below;
Now after creating the relationship; we can see the visualization works fine:
Behind the scene this means filtering like below:
Stor_id field is behind the scene acting to connect these two tables. The first table gets filtered by state, and then the second table (through the relationships of stor_id) also gets filtered for each state.
Other Properties of a Relationship
There are some other important properties of the relationships, which I am going to explain in the next few articles, such as:
- The cardinality of the Relationship (1-1, 1-Many, Many-1, Many-Many)
- The direction of the Relationship
- Active or Inactive Relationship
- Relationship Based on Multiple Fields
Summary
Relationships are one of the basics, however, one of the most essentials part of a Power BI modeling. If you are coming from the database background you already have a good understanding of relationships, If you are coming from Excel or business background, you can consider each relationship like a lookup table.
The relationship is what makes the filtering across multiple tables possible. In this article, you learned about the basics of relationships, such as why we need it, and how to create the relationships, and what happens behind the scene of a relationship logically.
In future articles, you will learn about other important features of a relationship.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Very useful, clear and valuable. Thank you Reza.