Forum Discussion
Performance issue to find Rolling52WeekAverage from the Measure value using by (SUMX & AVERAGEX)
Hi,
I am using the following two DAX formulas to find an average of Rolling52Weeks from the Measure value, the results are getting correctly but performance is very slow. I just filtering the prior 52 weeks from the calendar and applying into SUMX/ AVERAGEX in order to get a result.
Can anyone please help me if there is any way to replace the SUMX/ AVERAGEX for the below scenarios or can please confirm if I made any logical mistaken on the below DAX.
Appreciate if you join with me to get resolved.
Rolling52WeekAverage:=
var Filtervalue =
FILTER(ALL(CalendarWeekly),AND(CalendarWeekly[CalendarWeeklySkey] <=max(CalendarWeekly[CalendarWeeklySkey]),CalendarWeekly[FiscalWeekCloseDt] > max(CalendarWeekly[FiscalWeekCloseDt]) - 364 && Fact[Average_FlagNew] <> 3))
var Tempcnt =
COUNTROWS ( Filtervalue )
var rd = SUMX(Filtervalue ,Fact[Measure Value])
return DIVIDE(rd, Tempcnt )
Rolling52WeekAverage:=
var Filtervalue =
FILTER(ALL(CalendarWeekly),AND(CalendarWeekly[CalendarWeeklySkey] <=max(CalendarWeekly[CalendarWeeklySkey]),CalendarWeekly[FiscalWeekCloseDt] > max(CalendarWeekly[FiscalWeekCloseDt]) - 364 && Fact[Average_FlagNew] <> 3))
return AVERAGEX(FILTER(Filtervalue,IF(Fact[Average_FlagNew] <> 3 ,1)),Fact[Measure Value])
Regards,
Raj
Anonymous
I am not sure if the SUMX or AVERAGEX causes the issue, check your data model with best practices https://powerbi.microsoft.com/en-us/blog/best-practice-rules-to-improve-your-models-performance/Check the following modified measure.
Rolling52WeekAverage = var _skey = _CalendarWeekly[CalendarWeeklySkey] var _close = _CalendarWeekly[FiscalWeekCloseDt]- 364 var Filtervalue = FILTER( ALL(CalendarWeekly[CalendarWeeklySkey],CalendarWeekly[FiscalWeekCloseDt]), CalendarWeekly[CalendarWeeklySkey] <=_skey && CalendarWeekly[FiscalWeekCloseDt] > _close && [Average_FlagNew] <> 3 ) var Tempcnt = COUNTROWS ( Filtervalue ) var rd = SUMX(Filtervalue ,[Measure Value]) return DIVIDE(rd, Tempcnt )
2 Replies
- Fowmy
Super User
Anonymous
I am not sure if the SUMX or AVERAGEX causes the issue, check your data model with best practices https://powerbi.microsoft.com/en-us/blog/best-practice-rules-to-improve-your-models-performance/Check the following modified measure.
Rolling52WeekAverage = var _skey = _CalendarWeekly[CalendarWeeklySkey] var _close = _CalendarWeekly[FiscalWeekCloseDt]- 364 var Filtervalue = FILTER( ALL(CalendarWeekly[CalendarWeeklySkey],CalendarWeekly[FiscalWeekCloseDt]), CalendarWeekly[CalendarWeeklySkey] <=_skey && CalendarWeekly[FiscalWeekCloseDt] > _close && [Average_FlagNew] <> 3 ) var Tempcnt = COUNTROWS ( Filtervalue ) var rd = SUMX(Filtervalue ,[Measure Value]) return DIVIDE(rd, Tempcnt )