cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Quantum_Udit
Advocate V
Advocate V

Handling Bi-directional cross-filtering

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.

1 ACCEPTED SOLUTION
PaulDBrown
Super User II
Super User II

@Quantum_Udit 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
Quantum_Udit
Advocate V
Advocate V

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

Pragati11
Super User III
Super User III

Hi @Quantum_Udit ,

 

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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




PaulDBrown
Super User II
Super User II

@Quantum_Udit 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Hi, @Quantum_Udit , 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/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors