Forum Discussion
Bad DAX code? Weighted average calculation works, but is SLOW
I have 30,000 stores, and a table that records their status each day - including how many days have passed since each store has been serviced.
I've created a measure that tells me what the average number of days is since store service for the network:
Avg Days Since Service :=
divide(
sumx('Store Status Fact',
'Store Status Fact'[DaysSinceService] * [Store Count]),
[Store Count]
)This yields the correct answer, but is too slow to be usable - takes 1-2 minutes to get back to me.
Goal is to make a simple slice-able line time series chart tracking daily average days since store service.
What's a faster reformulation of this calculation?
1 Reply
- v-yulgu-msft
Microsoft Employee
Hi Anonymous ,
Sometimes, SUMX might decrease the execution performance. You could replace SUMX with SUM instead.
Tune your PowerPivot DAX query, don’t use the entire table in a FILTER and replace SUMX if possible
Besides, you could run DAX Studion to track performance.
Best regards,
Yuliana Gu