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.
Sample Dataset
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.
Both-Directional Relationship
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.
Summary
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.
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 handy,thanks Reza
The model you’ve mentioned as a demonstration is a Kimball-modelled dimensional model; it is an ideal model in all senses of the word. It should have absolutely excellent performance characteristics (this is the fastest way to model data in any typical database). No one disputes this (even the Data Vault guys use dimensional models as their presentation layer). Yet it requires bi-directional filtering to work and you say , “If your model needs the both-directional relationship in the majority of the relationships, then your model is not designed well.” In another blog post you actually state that this is the best kind of model (https://radacad.com/data-preparation-first-and-foremost-important-task-in-power-bi) so which is it – is a dimensional model the best way to go, in which case bi-directional filtering is fine, or is there some other way we should be doing it? Surely you don’t expect a cross-filter DAX function for every measure in a (potentially large) dimensional model. That would defeat the purpose of modelling in that way.
Hi Sam
This is a star schema dimensional model based on Kimball methodology as you mentioned. However, still not the star schema that works best! You cannot just look at a model and say this is star schema. You have to see the requirement for that model first, it might look start schema, but then when you look at the requirement, you see that to answer that requirement, the model is kind of a snowflake, you may need to combine multiple fact tables.
This model, despite the fact that looks star schema, it still needs improvements, because a requirement is to do cross filtering across multiple tables with a many to many relationships. So the model definitely needs to be changed, and it can be changed in a way that ends up again with a star schema that performs best with the current requirement.
Cheers
Reza
In the example above 158+155+157+158+153 = 781, NOT 606 !
What is Power BI doing to display 606 ?
Hi Gareth.
606 is NOT the sum of values above! so your calculation doesn’t work here. 606 is the total count of products. Obviously, we have some products that have been purchased in more than one education category. 606 is the correct value.
Cheers
Reza
Hello, by me the total count of product sold is actually 158 not 606 (distinct count)… And so as to get the shown result I dont need bidirectional filtering…
Hi Filip
maybe you are using the ProductKey in the FactInternetSales table, in that case, yes, you would have the correct number using distinct count.
There are many other ways to get the value correct for this sample. However, this sample is just to explain what is a both-directional relationship, not to solve this particular count of products challenge.
Cheers
Reza
I get 158 if filtering EngEducat from DimCustomer even if all of them (5) are selected. If mark Select all then i get 606!? Please if you can use much simpler data examples . For understanding principle how it works would be much better.
Hi Miha
158 means that there were only 158 products that have been sold. there are 606 products in the product table, but note that not all of them have been sold.
Cheers
Reza
Hi Sir,
What is the meaning of the line -“because it will create a loop of filtering sometimes” ,
kindly explain.
A loop scenario is like this:
table A filters table B, and table B filters table C, then table C filters table A again. If these are both directional. it means table A filters some data, but again through a path of filtering it gets other values filtered and unfiltered. The circular reference is the other name for it.
thank you! I learned how I shoud use the relationship! Take care
I knew that bi-directional relationships were not ideal but I did not know how to work around to get the result I needed. This helped me a lot! Thank you so much!
If DimCustomer filters FactInternetSales,
Can a column from FactInternetSales filter DimCustomer ?
It depends on the direction of the relationship as mentioned throughout this article
if direction is from Dim to Fact. Then Dim Can filter Fact, but not the other way around.
Thank you so much, I could not solve my table issue and finally I have found the solution ! thanks