Power Pivot Principles: Ambiguity of Bidirectional Relationships in Power BI

Welcome back to the Power Pivot Principles blog. This week, we are going to talk about the ambiguity of bidirectional relationships in Power BI.

In our previous blog, we introduced how to establish bidirectional filters in Power BI by changing the options in ‘Cross Filter Direction’ to Both when setup the relationship in the editor. This week, we are going to discuss the ambiguity that may arise from bidirectional relationships.

Let’s look at an example. Suppose we have five (5) data tables and the relationships map is as follows:

BI developers rely on bidirectional filters for the purpose of synchronising slicers in line with the discussion in our previous blog. Introducing bidirectional filters increases the complexity of the data model and may also create ambiguity in the model. This may happen where there are multiple pathways between tables. In this situation, the engine has multiple paths when filtering through one table to another. Therefore, it either finds a preferred path or it catches an error. In our scenario, the ambiguity derives from the pathways connecting the Calendar and Order tables.

Apparently, the Calendar table filters through the Order table. This is the direct relationship between the two tables. Also, Calendar can filter through Sales and Product and reach Order table which is a longer pathway. Therefore, we can see the two pathways:

The directional relationship is labelled with yellow arrow and the longer pathway is labelled with blue arrows. The engine analyses the two paths and decides that path 2 is preferred over path 1 since path 2 is based on a direct relationship. When we filter from Calendar to Order, the engine uses the shortest path. This scenario is simple enough to understand, but in some scenarios, the paths may be more confusing.

Let’s look at another case:

In this case, if there is a filter on Date and a filter on Customer, both filters can be applied to the Sales by path 1 and 3. Sales can filter through Product and reach Order, therefore, the Sales can filter Order by path 1. At the same time, Calendar can filter through Order as well by path 2. If we set up filter on Calendar only and want to filter through Order, path 2 is preferred due to the direct relationship established, however, if there are both filters on Customer and Date, which path does Calendar use to reach Order? The answer to this question is not simple. If Calendar and Customer are filtered, both filters are applied to Sales. Then Sales filters through Product which in turn filters Order by path 1. Also, path 2 indicates that Calendar also filters Order through the direct relationship. Therefore, both path 1 and path 2 are used when Customer is filtered. The data model is ambiguous in this case due to the multiple pathways for filtering Order table.

With a simple case containing only five tables, the bidirectional relationship has increased the complexity of the calculations. In practice where the data model contains dozens of tables, the scenario is much harder to understand. The more filters on different tables, the more ambiguity the data model can be. This is not to say bidirectional relationship is bad, actually there are a few cases that it helps to sync slicers for multiple data filters. However, we recommend that the BI developer should limit the use of bidirectional relationships in the data model and make sure the data model is not ambiguous.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.