Bi-Directional Filter Behaviours

[jasperj101]

Good Afternoon All, 

I have come across some issues when using bi-directional relationships, it would be great if you could share some explanations and further reading sources to help me understand the following behaviour: 

The crux of my issue is, clearing filters in a dimension table using ALL() / REMOVERFILTERS() does not remove filters that have propagate through a bi-directional relationship. 

I have made a simple model to demonstrate this: 

Customers has a many to 1 relationship with territories (which region they are in the country) which is a single direction, were territories can filter the customer list. Customers also has a one to many bi-directional relationship with Tags, this is a table that has many 'Tags' defined for customers such as if they are part of a reward scheme and various different metrics. This needs to be bi-directional in my use case as the model needs to calculate measures over tags with a customer context and also measures over customers with a tags context. 

I then display a measure that counts all customers regardless of filter context: 

As you can see, when displayed, even if there is a page filter for 'Territories'[Area], this filter context will be ignored and it will give the total count of customers 7: 

However, when using that same measure and applying a page filter to 'Tags'[Tag_Description] it returns:  

In this instance, the ALL() filter does not clear the filter propagated through the bi-directional relationship.

If I clear filters from the tags table, this does fix the issue. However, in my real scenario there are many such tables and the potential it will scale into more in the future, which will result in me needing to add new filter conditions to many different measures each time a new table is added... 

Any explanations/help on this issue would be greatly appreciated! 

Cheers,

J

[danextian]

Hi @jasperj101 

I tried to mimic your table but I think I am missing something as my test shows that ALL applied to customers returns ALL regardless of the filters applied to it from either of the other tables.

It is always a good practice to post a sample data/pbix and not just images. That aside, try including ALL (Tags) inside CALCULATE as well.

[jasperj101]

Hi, 

I have uploaded the test model here: 

https://drive.google.com/file/d/1JkXlY7wgTwcML27_XJJQp6W273qLk6uE/view?usp=drive_link

[v-zhengdxu-msft]

Hi @jasperj101 

When enabling the bidirectional cross filter, once you filter one table, you also filter all the tables on the “one” side of a relationship.

So when you filter rows on Tags, you implicitly filter Territories and Customers, thus affect the result.

Here is a sample for your reference:

Row Context and Filter Context in DAX - SQLBI

To solve this, you can add a ALL(Tags) to clear the implicit filtering:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jasperj101]

Hi @v-zhengdxu-msft, 

Many thanks for the reply, the solution you have provided is the way I have already solved this. 

However, I am still not understaning the different ways the implicit filtering is working in this model. Am I correct that the definition of implicit filtering is a filter that has propagated through a relationship from an explicit filter in another table? 

If that is correct, the implicit filter that propagates from Territories > Customers through the many to one relationship gets ignored when using ALL(Customers). However, the implicit filter propagating from Tags > Customers through the many to one bi-directional filter does not get cleared by ALL(Customers), still not understanding why the bi-directional relationship acts differently...