cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fraukje Advocate I
Advocate I

Performance issues with FILTER and other DAX functions

Hi all,

 

I was looking at a 30-day aggregate of some table, see below the measure I created:

 

ChecksLast30 = CALCULATE(SUM(MerchantStatistics[ChecksApi]) + SUM(MerchantStatistics[ChecksSF]),  FILTER('Date', 'Date'[Date] >= TODAY() - 30))

 

The table is big but not huge (~1M rows, and ~15 columns) but I noticed an immediate performance effect: the table in which I was displaying ChecksLast30 took about 30 seconds to load. Then I found online somewhere (here: http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques)

that some DAX functions do a row-by-row scan instead of bulk. And indeed, when I replaced the FILTER with a direct expression:

ChecksLastMonth = CALCULATE(SUM(MerchantStatistics[ChecksApi]) + SUM(MerchantStatistics[ChecksSF]), 'Date'[Date] >= TODAY() - 30)

the performance became very good (<~1 sec).

 

Does anyone know a good source to read up on this behaviour of FILTER and possible other DAX-functions related to performance?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Performance issues with FILTER and other DAX functions

Hi @Fraukje

This article provides a good explaination of using Filter vs not using Filter: https://powerpivotpro.com/2010/04/quick-tip-dont-over-use-filter/

View solution in original post

1 REPLY 1
Highlighted
Anonymous
Not applicable

Re: Performance issues with FILTER and other DAX functions

Hi @Fraukje

This article provides a good explaination of using Filter vs not using Filter: https://powerpivotpro.com/2010/04/quick-tip-dont-over-use-filter/

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors