Forum Discussion

Fraukje's avatar
Fraukje
Icon for Advocate II rankAdvocate II
8 years ago
Solved

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?