Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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/
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/
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |