Forum Discussion
Performance : calculating Moving average, percentile
Good morning, everyone, I would like to present to you a case where performance is poor. The context is this: a date dimension table (this one includes all dates with hh:mm:ss) + a fact table ("PDJ...
Can you try changing the last 2 bits:
'Fact'[TimeID] >= MovingWindowStart
&& 'Fact'[TimeID] <= MovingWindowEndto
'DimTime'[TimeKey] >= MovingWindowStart
&& 'DimTime'[TimeKey] <= MovingWindowEndIn addition, make sure TimeKey should be an integer.
Thank you AkhilAshok !
It looked good! But the average doesn't work.
You can see below.
AverageMoving2sTest =
VAR MovingWindowEnd =
MAX ( 'DimTime'[TimeKey] )
VAR MovingWindowStart = MovingWindowEnd - 2
VAR MaxTimeinFact =
CALCULATE ( MAX ( 'Fact'[TimeID] ); ALLSELECTED ( 'DimTime' ) )
VAR MinTimeinFact =
CALCULATE ( MIN ( 'Fact'[TimeID] ); ALLSELECTED ( 'DimTime' ) )
RETURN
IF (
MovingWindowEnd <= MaxTimeinFact
&& MovingWindowEnd >= MinTimeinFact;
CALCULATE (
AVERAGEX (
VALUES ( 'DimTime'[TimeKey] );
VAR Total = [Total bets/s] RETURN IF ( ISBLANK ( Total ); 0; Total )
);
ALL ('DimTime');
'Fact'[TimeID] >= MovingWindowStart
&& 'Fact'[TimeID] <= MovingWindowEnd
)
)
Regards,
AkhilAshok
Solution Sage
Solution SageCan you try changing the last 2 bits:
'Fact'[TimeID] >= MovingWindowStart
&& 'Fact'[TimeID] <= MovingWindowEnd
to
'DimTime'[TimeKey] >= MovingWindowStart
&& 'DimTime'[TimeKey] <= MovingWindowEnd
In addition, make sure TimeKey should be an integer.
v-frfei-msftCommunity Support
Hi Anonymous,
Does that make sense? Kindly mark the answer to close the case. Thanks in advance.
Regards,
Frank