THE DIRECTION OF RELATIONSHIPS IN POWER BI
In a data model set-up that holds many tables, there will be a need to link these tables to provide effective analytics. The link between two or more tables is called a relationship, hence if two tables are tied with a relationship, we say that they are related.
This concept is presented in the figure below:
Figure 1: A data view representation of the relationships between all three tables.
The relationship type that exists in the image above is one (1) to many relationships. The arrows indicate the relationships between the two tables and the direction of the relationship. The directions of the two relationships are from
1. Products to Order-details
2. Orders to Order-details
While it is important to establish relationships between two or more tables within a data model, the direction of these relationships must be observed. The way Power BI filters data depends on the Cross Filter Direction (direction of relationship between two tables).
In terms of the relationship between two tables (cardinality) and the direction of the relationship between the tables (cross filter direction), the table below provides more clarity.
At the moment, we will have a closer look at how these two types of cross-filter directions work. If we observe the relationship between the ORDER table and the ORDER-DETAIL table, we notice that the direction is from the ORDER to ORDER-DETAIL. This suggests that any column in the ORDER table can filter (break down) the data in the ORDER-DETAIL table as shown in Figure 2.
Figure 2: A Table of Unit Prices (Order Details) by ShipName (Order Table)
We can filter any column of the ORDER DETAILS table using any column from the ORDER table since the relationship between the two tables flows from the ORDER table into the ORDER DETAILS table.
If we try to filter the customer table by any column of the SALES table with the current relationship, it will give us an expected error where the fixed value from the SALES table will be on every row of the selection as presented in Figure 3.
With a sound application of CROSS FILTER both direction options, we can eliminate the error we encountered in this previous illustration. We can make these changes by navigating to the Manage Relationships tab under the home tab. A dialogue window will pop up showing all the active relationships in the model.
Figure 3: A table that counts the shipcity (ORDER Table) by OrderID (ORDER DETAILS Table) whose output is an error.
Figure 1 provides a representation of all the relationships within the data model (ORDER DETAILS table is linked and filtered by the ORDER table while the PRODUCT table relates with the ORDER DETAILS table).
Since we need to change the CROSSFILTER direction option for the relationship between the SALES table and the PRODUCT table, kindly select this relationship and click the EDIT button. This action will initiate a new window as shown below:
Figure 4: A dialog box that presents the EDIT relationship between ORDER DETAILS and ORDER Tables where cross filter direction is Single
In Figure 4, we see that the current CROSSFILTER direction for this relationship is Single. We can change the CROSSFILTER direction to Both in a bid to ensure the SALES table and PRODUCT table can filter themselves. Figure 5 shows the change we need to show the error we encountered earlier.
Figure 5: A dialog box that presents the EDIT relationship between ORDER DETAILS and ORDER Tables where cross filter direction is Both.
Adjusting the CROSS FILTER direction to bi-directional ensures we can filter the PRODUCT table by any column of the CUSTOMER table. Figure 6 presents this result.
Figure 6: A table that counts the shipcity (ORDER Table) by OrderID (ORDER DETAILS Table) with the correct output.
Both-directional filtering is a good tool business intelligence developers can employ when they evaluate the data modelling of any project. It is worth noting that the CROSS FILTER (BOTH) option has some shortcomings (filtering loop as well as performance issues) hence the application of this tool must be considered thoroughly before implementation.
Figure 7: A data view representation of the bidirectional cross filtering between ORDER DETAILS and ORDERS.
