Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not 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/

View solution in original post

1 REPLY 1
Anonymous
Not 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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.