Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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 )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Your DAX logic works fine and it is faster than earlier logic.
Thank you so much Fowmy!!
Regards,
Rajkumar
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |