Power BI Relationships
In Power BI we will most of the time work with data models that contain data from multiple tables that must be connected through relationships. Well prepared and thought through tables that are connected by relationships are required between these tables to function together to allow visualizations to be sliced and diced by data from different tables.
In this blog post, we will cover the essentials of relationships in Power BI modelling. We will not be covering why multiple tables are necessary and the art of data modelling among multiple tables in this blog post.
When you import multiple tables, chances are you’re going to do some analysis using data from all those tables. Relationships between those tables are necessary in order to accurately calculate results and display the correct information in your reports. Power BI Desktop makes creating those relationships easy.
Once you have connected two tables together with a relationship, you can work with the data in both tables as if they were a single table, freeing you from having to worry about relationship details, or flattening those tables into a single table. In many situations, Power BI Desktop can automatically create relationships for you, so creating those relationships yourself might not even be needed. However, if Power BI Desktop can’t determine with a high degree of certainty that a relationship exists between two tables no automatic creation of relationships will take place. In that case, you must create a relationship.
Creating relationships manually
Let’s start off by creating a manual relationship between two of our tables, our ‘Products’ table containing all information with regards to our products and our ‘Transactions’ table containing all our transactional data of sales.
A relationship can be created in one of two ways:
Firstly, by selecting ‘Manage Relationships from the ‘Home’ tab.
Secondly, by clicking and dragging the column that you want the relationship to be formed by from one table onto the column of another table.
Let’s start from scratch and use the above example to create a relationship between our ‘Products’ and ‘S_Transactions’ tables so that the tables are connected through relationships by selecting ‘Managing Relationships’. Let’s select ‘New’.
Select the two tables that you want to connect through a relationship as well as the columns (fields) that will connect these tables. You will notice that Power BI automatically tries and identify the columns that will connect your tables. When your tables are set up well, Power BI will mostly get this right. You can see from the image below that Power BI identified the ‘ITEMCODE’ column as the field by which we will establish our relationship. This is the column that we would like to use. Just note here that column names do not have to be the same to connect tables.
Now that we have established the columns which will bind our tables is it necessary to edit our relationships.
Once your tables are connected through a column from each table is it important to edit your relationships. Here we will look at two aspects:
- Cross filter direction
You will notice when setting up relationships Power BI Desktop automatically configures the Cardinality, Cross filter direction, and Active properties; however, these settings are adjustable and should always be verified.
Let’s explain the Cardinality and Cross filter direction before continuing with our example.
Relationships between tables can have three different types of cardinality.
- Many to One (*:1) – This relationship occurs most often, default type. This means that a column in one table can have one instance of a value and this is your lookup table. The other column may have numerous instances of the value.
- One to One (1:1) – Both tables relational column has only one instance of a value.
- Many-to-many relationships: This relationship configuration comes with a few benefits. As two tables have a many-to-many relationship it removes requirements for unique values in tables.
Filter direction of Relationships
The most important question is what the direction of a relationship means. This sets the filtering direction of your tables. The direction of the relationship refers to how Power BI filters the data. Let’s use our data as an example. The relationship between our S_Transactions and Products tables indicates that the direction of the relationship is from the Products table to the S_Transactions table.
Thus any column from the Products table can filter the data in the S_Transactions table. The inverse is not true. Let’s create a table from the Category field in the Products table and the Total Sales from the S_Transactions table. The Category field filters the data in the S_Transactions table to summarize the Total Sales per Category.
Let’s test the inverse of the above where we try and filter the Products table from the S_Transactions table. We will create a table from the Itemcatcode field in the S_Transactions table and count the number of stores that stock that specific Itemcatcode from the Store field in the Products table. Take note of our result below:
You will notice what is odd here is that we have for each Itemcatcode a count of 17 stores and the Total value is also 17. This is incorrect as we know that not all our Itemcatcode’s is in every store. Furthermore, the total value cannot be 17 if all our Itemcatcode’s are 17. The data is displayed incorrectly due to our filtering direction. We can only filter the S_Transaction table from the Products table. The inverse is not true due to our filtering direction being only from the Products to the S_Transactions table.
To filter from our S_Transactions table to our Products table we need to change the filtering direction to Both.
Let’s see how the table has updated with this change.
This is quite useful to know and understand. Just an important point is that you might think that you can always implement the Both directional relationship. However, this might cause some serious performance issues. Although it is a useful function to have it is the last resort when you really cannot get the results you would like through sound data modelling in Power BI. Use this Data Bear series on data modelling to make sure that you are comfortable with data modelling in Power BI.
We covered the basics data tables connected through relationships in this blog post. Although relationships are easy to understand it is critical to get right. It forms the base of your data model and allows you to build powerful reports. In this blog post you have gained a good understanding of the importance of relationships and how to set up relationships. In future posts, we will cover more details of relationships in Power BI.