Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

    Can 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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you. It's a great idea about two seperate dimension tables for Date and Time.
I could reduce the performance.

 

But how do you fill the lines without measures ? To calculate average moving, you have to find all the seconds into the fact table.

And from my source, i have holes because i don't have all the seconds. i have to load the other lines with a measure to 0 with a outer join in Power Query (Merge between dimension date&time and the fact table)

 

Without index, it means that you use time for the calculation, isn't it ?

 

May be with this calculated measure :

MovingAverage = CALCULATE(AVERAGE([Value]),FILTER(MovingAverage, EARLIER([Time])>=[Time]))

 

Thank you.

AkhilAshok's avatar
AkhilAshok
Icon for Solution Sage rankSolution Sage
7 years ago

You should have a Date dimension (365 rows for 1 year) & a time dimension (86400 rows for one day), and link it to your fact table.

 

Afterwards, you can derive your moving Average based on the Time dimension table which has all the time.

 

AverageMoving2s =
VAR MovingWindowEnd =
    MAX ( 'Time'[TimeID] )
VAR MovingWindowStart = MovingWindowEnd - 2
VAR MaxTimeinFact =
    CALCULATE ( MAX ( 'Fact'[TimeID] ), ALLSELECTED ( 'Time' ) )
VAR MinTimeinFact =
    CALCULATE ( MIN ( 'Fact'[TimeID] ), ALLSELECTED ( 'Time' ) )
RETURN
    IF (
        MovingWindowEnd <= MaxTimeinFact
            && MovingWindowEnd >= MinTimeinFact,
        CALCULATE (
            AVERAGEX (
                VALUES ( 'Time'[TimeID] ),
                VAR Total = [M1 Total] RETURN IF ( ISBLANK ( Total ), 0, Total )
            ),
            ALL ( 'Time' ),
            'Time'[TimeID] >= MovingWindowStart
                && 'Time'[TimeID] <= MovingWindowEnd
        )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      AkhilAshok
      Icon for Solution Sage rankSolution Sage
      7 years ago

      Can 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-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous,

         

        Does that make sense? Kindly mark the answer to close the case. Thanks in advance.

         

        Regards,

        Frank