Navigating Cross Filtering Challenges in Power BI Data Modelling

I stumbled across bi-directional cross filtering when working with a dataset which at the outset looked simple and straightforward, however, threw a few curveballs at me when I got into analysis and data visualization. The good part is that I learnt a few important lessons while working on the dataset and I am here to share with you some of those learnings.

Before getting into that, it will be helpful to have a short introduction on data modelling in Power BI. Data modeling involves creating visual representations of connections among data structures, showcasing details about individual attributes within those structures. The star schema is the most popular model thanks to enhanced usability, clarity and organization, leading to user-friendly reports, and improved performance.

How to build data relationships in a model ? Go to model view and you can drag a field from one table onto the corresponding field in the second table, forming an immediate connection. Alternatively, by navigating to the ribbon and selecting "Manage Relationships," you can create a new relationship. Although Power BI automatically infers relationships between tables by default, it may not always be accurate. Users have the option to disable this feature in settings or delete automatically generated relationships. To modify relationships, right-click the connecting line and choose "Properties" to fine-tune settings like cardinality, filter direction. I will be explaining them in details.

 The dataset I worked with was from a business having multiple stores across different regions and the data included details of orders such as order and shipping dates, product specifications, returns, regions, customers, managers. This sounds like a simple star schema with one-to-many relationship between the dimension tables (Returns, People, Date) and the fact table (Orders). The logic being – dates in Date table, managers/employees in People table and details of returns in Returns table are associated with multiple orders in the Orders table.

When the model is prepared it looks something like this -

Fig. 1 - One to many unidirectional relationship, notice the arrowhead direction

Fig. 2- Cross filtering direction is single

This is the time to briefly discuss Cardinality in Power BI models.

Each model relationship is defined by a cardinality type. There are four cardinality type options, representing the data characteristics of the "from" and "to" related columns. The "one" side means the column contains unique values; the "many" side means the column can contain duplicate values.

Types of Cardinality

There are four types of cardinality, as below:

1-1: one-to-one

1-*: one-to-many

*-1: many-to-one

-: many-to-many

Let’s review the different aspects of our model –

1                                         One

*                                         Many

A tiny arrowhead         Indicates the direction of filtering.

Pay close attention to the arrowhead. The direction of the arrowhead is important and indicates the filter direction, for bi-directional filters there are two arrowheads next to each other and pointing to opposite directions.

Now, it gets tricky if one tries to do an analysis of returns, keeping the model as is. The result of return order by market shows incorrect result – every market having same number of returns. Of course, this is not possible and we have to analyze the situation.

Fig. 3 - Incorrect result with uni-directional filtering

Fig. 4 - Data pane

What happens in uni-directional filtering vs. bi-directional filtering ?

If the analyst or marketer filters Return table and chooses any Order ID, the filter will also be applied on the Orders Table. But if I choose any Order ID from Order table, the filter won’t be applied on Returns table.

Applying this logic to the current problem – when choosing orders from Returns table and 'market' from Orders table, the model is picking up all orders ( 1079) while not doing the market-wise filtering since the direction of filter in the one-to-many relationship was unidirectional.

We expect to resolve this issue if the filter direction goes both ways, i.e., bi-directional cross-filtering.

Fig. 5 - Bi-directional Cross Filtering, notice the arrowheads

How to set bi-directional cross filtering ?

Fig. 6 - Setting Cross Filter as 'Both'.

Since cross filter is set to both, let's test if the analysis changes -

Fig. 7 - Pie chart with correct outputs market-wise

Our problem seems to have resolved as we get market-wise data . Also notice that the total adds up to 1079, this confirms that in absence of bi-directional filtering, the model was simply adding up all orders.

Does this mean that we should use bi-directional cross filtering whenever we encounter one to many relationships and problems like this ?

Not really ! If you think about it, one-to-one relationships are bi-directional by default. It totally depends on the requirement. Bi-directional relationships can cause performance issues and ambiguity in analysis specially when dealing with a larger more complex dataset. Instead of incorporating bi-directional relationship in the model, one can create measures using CROSSFILTER DAX function to meet a specific analysis or visualization requirement. This will keep the model optimized in terms of performance as well as mitigate the risk of ambiguity.

For our problem at hand, another solution is available where we can add a calculated column Market to Returns Table which will lookup Market from Orders table using LOOKUPVALUE function.

Market = LOOKUPVALUE(Orders[Market], Returns[Region], Returns[Region])

This will yield the same result as changing filter direction.

Play around with these concepts in your next analysis, which one would you use ? Leave your ideas in the comments.

Further reading -

Davidiseminger. (2023, October 27). Model relationships in Power BI Desktop - Power BI. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand