Power BI Relationship and Cross Filter Direction

Power BI Relationship and Cross Filter Direction

In most database management systems, you only have a relationship and no direction for it but this is not so in power bi, in power bi, we both have to consider relationships and directions in other to build an effective model to achieve an interactive report. The direction of a relationship plays an important role the same way filtering works in power bi.

At the end of this post, you will learn about what the direction of the relationship is, and what is the difference between both directional and single-directional relationship. in the screenshot below, you can see the direction of the relationship is from Customer Table to the FactSalesTable. It means any column from Customer Table can filter the data in the FactSalesTable. As an example, you can slice and dice the Sales (in the FactSalesTable) using the ID Customer (in the Customer Table), as below.

Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction

This may look straight and easy but there are times where you need to filter tables against the direction of the relationship. Let us look at an example. Let’s say we want to compare Car ID, Unit sold on Car Sold Table with Unit Produced from Car Produced Table based on the previous relationship as seen below.

Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction

When the report is built on Table the result is shown below tells us that the Car Produced table is not related to the Car Sold Table neither are the Columns.

Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction

As you can see in the above screenshot, the sum of Unit Produced is 36000 for every ID Car/Units Sold The reason is The DIRECTION of the relationship. 

As you can see in the above screenshot, only filtering from Car Color to CarSold and Car Color to Car Produced is allowed with the current direction of the relationship. However, what we are trying to achieve in this example is a bit different.

For the example above to work, you need to change the direction of the relationship to both-directional to get it working as shown below.

Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction
Power BI Relationship and Cross Filter Direction

Hope you found this helpful!

Do not forget to check out our training courses: https://www.urbizedge.com.

One thought on “Power BI Relationship and Cross Filter Direction

  1. Pingback: Power BI Desktop: The Beginner’s Guide - UrBizEdge

Leave a Reply

Business Data Analysis
close slider
[]
1 Step 1

Interested in our Business Data Analysis Training?

keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right