Cross Filter Both Direction

Data Visualizations
1

Hi everyone
My fact table has some lookup tables. I have this visual, that we can choose client name, product, cost center, etc.
What i would like to see is, when i choose client name, for example, the filter box of product shows me only what that client bought us, and the same for other search boxes.
What i did, i’ve created a relation between clients table and sales table, many to one of course, but the cross filter in both directions.
Is this wrong? Why? It seems working…
Thanks a lot
Pedro

2

Hi @pedroccamara,

You just need to be careful about perfomance. Once both tables can be filtered by each other, it can be an issue if you have a big dataset.

Also, you can have some ambiguity problems. One table filtering other that is filtered by others…

Check this link for more references:

2 Likes
3

Hey @ricardocamargos88
Thanks for your answer. I think what i’ve learn on that document(and another video from Raza and other from Ruth) is that if i have some relations with both directions it will slow down the performance of our calculations and sometimes with an error. I would understand if my fact table field wasn’t hidden so i couldn’t use it in the future.
This is a simple filtering and i hope in the future this is solved because there’s a much more friendly visual when you choose a customer and you get to see, his country filtered, and products filtered , without messing the performance of our model.
But thanks anyway for your answer
Best regards
Pedro

4

You could use CROSSFILTER inside CALCULATE to just enable bi-directional filtering for the duration of CALCULATE example:

=
CALCULATE (
    [Total Sales],
    CROSSFILTER ( Sales[ProductKey], Product[ProductKey], BOTH )
)
1 Like
5

@pedroccamara,

I completely agree with @ricardocamargos88 and @AntrikshSharma. When I do my data modeling, I pretend that bidirectional filtering doesn’t even exist. The vast majority of even complex data models if set up properly can meet all the necessary analytical requirements without bidirectional filtering. Mastering TREATAS will also help deal with a large number of the many-to-many situations you may encounter without having to revert to bidirectional relationships.

  • Brian

https://info.enterprisedna.co/dax-function-guide/treatas/

2 Likes