Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 pr...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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 )



Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

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 )



Anonymous's avatar
Anonymous
Not applicable
5 years ago

Your DAX logic works fine and it  is faster than earlier logic. 

 

Thank you so much Fowmy!!

 

Regards,

 

Rajkumar