Activating bidirectional/two-way cross-filter during a tabular data model may produce ambiguous paths in the chain of relationships, resulting in very dangerous models as numbers become unpredictable. This blog provides an in-depth clarification of the kind of ambiguity which may seem when using two-way/bidirectional relationships.
You may set a relationship to be unidirectional–its default actions–or bidirectional. In a unidirectional relationship, the background of the filter is propagated from one side to the other, but not the other way around. In other words, a Consumer Filter automatically filters Sales in the following example, whereas a Sales Filter does not spread either to Product or Customer or Date.
This behavior works well in most scenarios. Indeed, a report generally slices sales amounts by a customer or product attributes. It’s quite uncommon to filter customers, or products, based on sales.
Therefore, why would one enable bidirectional filtering? The most common reason is to sync slicers.
Look at the following report where there are two slicers–one for the customer name and one for the product color, with a matrix providing sales details:
The report does work well; it reveals the purchases made by Amanda. Nevertheless, the color slicer does not filter out the few colors that Amanda has bought. One can easily see which colors she purchased by adding the color to the chart, yet the slicer doesn’t provide the user with simple feedback. The explanation for this is that the customer’s filter hits Sales. Therefore, it only filters the selected customer’s income. But the filter does not necessarily flow from Sales to Consumer, as a default unidirectional relationship is established between Product and Sales.
Product–Sales Relationship as a Bidirectional Relationship
There is a simple solution to the problem: set the Product–Sales relationship as a bidirectional relationship. By doing so, the filter automatically propagates from Sales to Product, so that the slicer displays only the chosen customer’s products (thus, the colors). The result is fairly nice:
Many Power BI users are aware of bidirectional filters for slicer synchronization purposes. The software was clear and sound with unidirectional filters. The relationship update looked simple and realistic. Unfortunately, the data model has now been transformed into a data monster through the enabling of a simple bidirectional filter, whose behavior is complex enough to be unpredictable. That’s a strong statement, and that clarification is dedicated to the rest of the blog.
The resulting pattern is not so different from the original model. The only (minor, not very noticeable) difference is the presence of a double arrow on the Sales–Product relationship.
Now look at the model before moving on with the article, and check where the ambiguity is. You need to find two tables that are connected by different paths, following the cross-filter arrows.
Sure, have you just found them? Fantastic! you discovered you could hit Purchases in two different ways, starting from Date:
- From Date to Purchase: this is the direct relationship between the two tables in the figure below, marked as (1).
- From Date to Sales, to Product, to Purchases: this is a longer route, marked as (2) within the figure, which is entirely legit.
The figure below shows the two paths:
We produced an uncertain model, and yet the engine didn’t complain. The explanation for this is that the engine determined by comparing the two paths that one direction should be preferred to the other. In this case, the choice seems straightforward: since one of the two relationships (1) is direct, it’s the preferred path. The engine, therefore, uses the shortest path when a filter needs to be moved from Date to Purchases and avoids the longer one.
This is an artificially simple scenario: There are two paths, one of which is the best. Making more complex scenarios where that option is no longer clear is completely simple. However, the engine’s algorithm for selecting the desired route isn’t that straightforward. It selects the shortest path wherever the primary relation is closest to the filter. If this last phrase sounds confusing, then that is by design. The algorithm is extremely complex for a human to analyze and comprehend. The algorithm, on average, almost always chooses the path a human would have chosen, but its definition is much more complicated to be a generic algorithm. We are not going to expand further on that.
Though one path is preferred over the other, there are still two possible paths from Date to Purchase to propagate the filter. Both paths can be followed in a single expression in some situations resulting in ridiculous results; let’s dig into this further.
We know that by following the most direct relation, a filter moves from Date to Purchase. The figure below shows the full model, with colored and numbered paths.
We authored three different measures. The below measure is just the purchase amount.
Purchases = SUMX(Purchass, Purchases[Quantity] * Purchases[Unit Cost])
The below two measures disable one of the conflicting relationships: either the relationship between Date and Purchase or the relationship between Date and Sales:
1.) Purchases = CALCULATE ( [Purchases], CROSSFILTER ( 'Date'[Date], Purchases[Order Date], NONE ) ) 2.) Sales = CALCULATE ( [Purchases], CROSSFILTER ( 'Date'[Date], Sales[Order Date], NONE ) )
No need to comment on every single number. It is sufficient to note that the matrices produce three distinct results. When CROSSFILTER, disables one of the two relationships, then Date filters Purchases using path 2 or 3.
When each relationship is active, Date filters Purchases through the combination of all three paths, as we mentioned earlier.
As you noted, this blog is not about showing the power of DAX or some interesting algorithms. This blog demonstrates how dangerous bidirectional cross-filter relationships are with a very simple data model containing only five tables. The presence of a bidirectional cross-filter relation made the calculations much more difficult to understand. The situation is much tougher in the real world, where it’s normal to have models with hundreds of tables.
So, we recommend that our readers stay away from having in the data model two-way/bidirectional cross-filter relationships. Bidirectional relationships are not bad or are a useless feature. There are some cases where bidirectional cross-filter functionality is useful.
Nonetheless, these must be carefully leveraged to ensure the model does not become ambiguous due to the bidirectional relationship. Implementing bidirectional cross-filter for the use of syncing slicers is not a good idea. After all, you don’t turn on the Death Star if you need to kill ants at the yard.
This is not to suggest that in any way, synced slicers are evil-they’re great. If slicer syncing came as a feature in Power BI, it would be great. So the users would stop using bidirectional relationships for that purpose.
Next time you are tempted to enable bidirectional cross-filter on a relationship, return to the current blog; read it once more and take another shot at it.
We must be very careful while creating relationships between the tables. We hope you have understood the concept of a bidirectional relationship. For any query, please comment in the comment section. To know more about our services, please visit Loginworks Softwares