Hi DAXians! In the last tutorial, we went through the basics of DAX and data modeling. Understanding data modeling is crucial to dive deeper into the world of business intelligence. In this tutorial, we will continue our journey of learning the essentials of the data modeling world. We will understand the direction of relationship in Power BI. Let’s begin!
Types of Relationships
Relationships can be of two types:
- Unidirectional relationship
- Bidirectional relationship
Let’s understand them in detail.
Unidirectional relationship
In unidirectional relationship, filter propagation always occurs from one side of relationship to another side. Number of arrows represent the direction of a relationship in Power BI. The presence of single arrow on the relationship indicates that the relationship is unidirectional. For example, in the image given below, the direction of relationship starts from DimProduct and goes to FactSales table.
One thing to note here is that the columns in DimProduct table can filter FactSales table. But, any column from FactSales table can’t do so. It is because the direction of filtering is unidirectional, not bidirectional.
Bidirectional Relationship
In bidirectional relationship, filtering happens from both the sides of the relationship. That is, both tables involved in the relationship can filter each-other. The presence of two or double arrows on a relationship indicates bidirectional filtering. For example, in the image given below, both DimProduct and FactSales can filter each-other.
Here, any column from FactSales can filter DimProduct table which was not the case in unidirectional relationship.
A case-study
Unidirectional Relationship
Now, let’s add one more table to our data model here, i.e., DimDate as shown in the image below:
We can also note that in case of DimDate and FactSales, the direction of filtering is unidirectional. The filtering happens from DimDate to FactSales. We can say that both DimProduct and DimDate can filter FactSales, but, FactSales can’t do so. Also, DimProduct and DimDate do not have any cross-filtering present between them, i.e., DimProduct can’t filter DimDate and DimDate can’t filter DimProduct.
Let’s now look at the table given below:
- CalendarYear column of the table belongs to DimDate table.
- Count of ProductKey is taken from DimProduct table which is not directly connected to DimDate table. This is because the path of filtering breaks at FactSales table due to the presence of unidirectional relationships. Thus, it displays same number, 2517, for all the CalendarYears.
- Sales Amount is taken from FactSales column. Here, Calendar Year from DimDate table is able to filter FactSales table and thus, correct Sales Amount is displayed.
Bidirectional Relationship
Now, with bidirectional filtering applied between DimProduct and FactSales, the data model becomes like the following:
Now, start looking from the right side of the model. Cross-filtering propagates from DimDate table and reaches FactSales table. Now, due to the bidirectional filtering, the filter propagation doesn’t terminate. It goes on and reaches DimProduct table. Thus, in our model, due to the presence of bidirectional filtering between DimProduct and FactSales, any column from DimDate can filter DimProduct table. The result is the following table:
Now, the values in the Count of ProductKey column is also varying. That is, filter from CalendarYear is propagating to DimProduct table.
If we start looking from the left side of the model, we have DimProduct table filtering FactSales. But, the filtering path ends at FactSales and doesn’t reach DimDate table due to the presence of unidirectional filtering between DimDate and FactSales and the inability of FactSales to filter table confirms it:
Here, ColorName column belongs to DimProduct table. Count of Calendar Year is coming from DimDate table and Sales Amount comes from FactSales table as before. All the values in Count of CalendarYear are same, i.e., 7. Thus, ColorName can’t filter correct count of CalendarYear.
In the first thought, it may seem that bidirectional relationship is amazing and very useful. However, in the world of data modeling, it is recommended to avoid bidirectional relationship as much as we can. Why? We will study in the future tutorials. Till then, stay tuned. Also check out our YouTube channel, ML For Analytics.
