What is the Direction of Relationship in Power BI?

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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;

Enter Your Email to download the file (required)

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

9 thoughts on “What is the Direction of Relationship in Power BI?

  • 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

    • 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

  • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *