Handling Bi-directional cross-filtering

[quantumudit]

Hello guys, Let’s say I have a simple model and I fully understand how bi-directional filtering works and I really need to optimize my model performance.

So, what is the best way, and why so?

The options are:

Opt-1: Enabling Bi-directional cross-filtering ?

Opt-2: Use CROSSFILTER() function instead ?

Notes: I do understand that bi-di are troublesome and we must avoid using it but, I just need to understand what is the best approach when we have a simple model.

So, this means my model is simple but, not the dataset, so it could have millions of rows and 20-30 columns.

[quantumudit]

Thanks, @Pragati11 , @CNENFRNL  & @PaulDBrown  for your valuable inputs. 😊

[Pragati11]

Hi @quantumudit ,

I agree with @PaulDBrown. Use CROSSFILTER function.

Best practice is to avoid Bi-Directional relationships as they sometimes introduce complex joins between the tables and affect background performance a lot; specially when you are dealing with millions of rows in the dataset.

Thanks,

Pragati

[PaulDBrown · Accepted solution]

@quantumudit 

Personally I would avoid bi-directional filters at all costs (I've only used them for RLS security tables), and go for TREATAS ( I use this function very often) or CROSSFILTER

[CNENFRNL]

Hi, @quantumudit , in addition to PaulDBrown's advice on CROSSFILTER and TREATAS, I'd like to recommend Expanded Table as a third option. It's a more intrinsic way of filter propagation in DAX. Pls refer to this article by Italians.

https://www.sqlbi.com/articles/expanded-tables-in-dax/