Relationships in Power BI are a bit different from other database management systems. In most of the systems, you just have a relationship and there is no “Direction” for it. In Power BI, however, there is a direction for the relationship. The direction of a relationship plays a critical role in the way that filtering works in Power BI. Understanding the direction of the relationship is an important step towards the modeling in Power BI. In this post, you will learn about what the direction of the relationship is, and what is the difference between both directional or single-directional relationship. We will not talk about how to resolve the relationship issues in this post because it will make this post really long post. Later on, I will write about the best practices for covering both directional relationships. To learn more about Power BI, read Power BI book from Rookie to Rock Star.
If you want to walk through the example of this post, create a new Power BI Desktop file, and get data from AdventureWorksDW and select DimEmployee as the only table to get data from. Here is how to access the AdventureWorksDW dataset;
Open a new Power BI Desktop, and Get Data from AdventureWorksDW and select these tables; DimCustomer, DimProduct, FactInternetSales. Load the data into Power BI. After loading data into Power BI, In the relationship tab, you should see all three tables related to each other. You can see the direction of relationship which is usually from one side of the relationship to the “many” side of it.
What is the Meaning of the Direction of the Relationship?
The most important question is what the direction of a relationship means? The answer is; It means Filtering. Whatever direction of the relationship is, that means how Power BI filters the data. in the above screenshot, you can see the direction of the relationship is from DimCustomer to the FactInternetSales. It means any column from DimCustomer can filter the data in the FactInternetSales. As an example; you can slice and dice the SalesAmount (in the FactInternetSales table) using the EnglishEduction (in the DimCustomer), as below;
So far, nothing seems strange or weird. You can slice and dice the data of the fact table also using columns from DimProduct, because there is a relationship direction from DimProduct to FactInternetSales. The problem (or let’s say the strange part) comes when you want to filter on a different direction than what is defined in the relationship. Let’s see an example.
Direction of a relationship means the way that filter propagates in Power BI
Filtering Based on Different Direction
There are sometimes that you need to filter against the direction of the relationship. Let’s look at an example. Let’s assume you want to get the number of products been sold in each education category. You can create a table with EnglishEduction (from DimCustomer), and ProductKey (from DimProduct) as the first step. Then in the field’s list of the visual, change the aggregation of ProductKey to Count as shown in the screenshot below;
The result would be the count of products for each customer education category. But wait, the result doesn’t look correct! It shows 606 for every education category!
As you can see in the above screenshot, the count of ProductKey is 606 for every EnglishEducation. The reason is The DIRECTION of the relationship. Let’s look at the direction again.
As you can see in the above screenshot, only filtering from DimProduct or DimCustomer to the FactInternetSales is allowed with the current direction of the relationship. However, what we are trying to achieve in this example is a bit different. We want to filter DimProduct based on the selection of Education in DimCustomer.
You do need to have a different direction in the relationship to get it working.
For the example above to work, you need to change the direction of relationship to both-directional to get it working. Please note that you SHOULD NOT do this all the time. The both-directional relationship has a significant drawback about performance (we will talk about it later). For now, to see what the both-directional relationship does, double-click on the relationship line between DimProduct and FactInternetSales and make it both directional.
Now you should see the result correctly in the table;
The reason that both-directional relationship works here is that it will enable filtering towards DimProduct as the below screenshot illustrates;
So, here you go; now you know what the direction of the relationship is, and what is the difference between both directional and single directional relationship. Before you go; you need to read one critical important note about the both-directional relationship, however!
Be Careful! Performance Considerations for Both-Directional Relationship
After you’ve done the example above, it seems that both-directional relationship is good! Then you may think; why we should not be using it all the time! If you have been using Power BI Desktop in the early days of the second half of 2015, the default type of direction was both-directional. That time, I got many calls and emails from people that their model is slow! Why you think was that? Because of both-directional relationship!
Both-Directional relationship is one of the ways you can kill the performance of your Power BI Model!
Yes, you read it correctly. The both-directional relationship is causing performance issues. Also, you cannot always create both directional relationship, because it will create a loop of filtering sometimes! So what is the solution? The solution of both-directional relationship is not short enough to talk about it in this post. I will just point out two methods, and then later on in future posts, I’ll explain them in details.
Method 1: Change the Data Model! Design Appropriately
Yes, The right data model does not need many places to be marked as a both-directional relationship. If your model needs the both-directional relationship in the majority of the relationships, then your model is not designed well. I have explained a bit about modeling principles in this post. I will write more about it later. A good modeling can resolve the need for both-directional relationship.
Method 2: Using CrossFilter DAX function ONLY IF the first method does not work
Only and only if you have designed your model properly, and still you cannot get what you need, then you can write a DAX expression using CrossFilter to get the result you want. Doing it this way is still using both-directional relationship for that calculation. however, the both-directional relationship would be used only for calculating that single measure. all other times, performance should be normal. I will write more about CrossFilter function in DAX in another post.
The direction of the relationship plays a very important role in modeling in Power BI. The direction of the relationship means the way that filter propagates in Power BI. The single-directional relationship will filter one table based on the other one. Sometimes you need to filter in a different direction, that is when the both-directional relationship comes into play. However, both directional relationship comes with a cost of performance issues. Do not use both-directional relationships blindly. Make sure you have designed your model in the right way first, and if that doesn’t work, then try other methods such as CrossFilter DAX functions. I will write later about how to resolve the both-directional issue in a Power BI model.