Forum Discussion
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?
- Anonymous8 years ago
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/
1 Reply
- AnonymousNot applicable
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/