Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Performance : calculating Moving average

Hi Everyone,   I am facing some performance issues when i try to calculate moving average on data. I do not have time dimension but i have some indexes that represent time (1,2,3,...) and some valu...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous -

    The query is slow because there are no grouping indexes. Without index(es) to group the rows, it will iterate the entire table for each row.

     

    The solution that I tried earlier didn't group them into big enough groups. You will need to optimize the exact number and size of grouping, but the concept is this:

     

    //Create the Table
Moving Average DAX = 
ADDCOLUMNS(
    GENERATESERIES(1,20000,1), 
    "MyValue",
    CEILING(RAND()*100,1)
)

//Rename the first column idx, for clarity

//Calculated Columns:
//This consists of a pair of groups at each level, to cover forward and backward. You can adjust the number and size of groups, but the smallest group needs to cover the necessary range.
GroupLarge1 = rounddown(DIVIDE('Moving Average DAX'[idx], 500),0)
GroupLarge2 = rounddown(DIVIDE('Moving Average DAX'[idx] + 250, 500),0)

GroupSmall1 = rounddown(DIVIDE('Moving Average DAX'[idx], 20),0)
GroupSmall2 = rounddown(DIVIDE('Moving Average DAX'[idx] + 10, 20),0)

//Measure:
//
Moving Average = 
var cur_idx = MAX('Moving Average DAX'[idx])
var cur_grp1 = MAX('Moving Average DAX'[GroupSmall1])
var cur_grp2 = MAX('Moving Average DAX'[GroupSmall2])
var mytablarge = DISTINCT(
    UNION(
        ALLEXCEPT('Moving Average DAX', 'Moving Average DAX'[GroupLarge1]), 
        ALLEXCEPT('Moving Average DAX', 'Moving Average DAX'[GroupLarge2])
    )
)
//If you want to add more grouping levels, you'll need to create a pair of variables for each level and create a table variable for each level
var mytabsmall = DISTINCT(
    UNION(
        FILTER(mytablarge, [GroupSmall1] = cur_grp1), 
        FILTER(mytablarge, [GroupSmall2] = cur_grp2)
    )
)
return AVERAGEX( 
    FILTER(
        mytabsmall,
        AND([idx] -10<= cur_idx , [idx] +10>= cur_idx)
    ),
    [MyValue]
)

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous -

Try this:

 

MovingAverageMeasure2 = 
var cur_time = MAX(DataForMA[Time])
CALCULATE(
    AVERAGE(DataForMA[Value]);
    FILTER(
        ALL(DataForMA);
        AND(
            DataForMA[Time] -10<= cur_time; 
            DataForMA[Time] +10>= cur_time
        )
    )
);

Hope this helps,

Nathan

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Nathan,

 

I had also tried a version of the measure with a calculate instead of an averageX (same as yours actually). This is also working but it still has terrible performance issues. And assigning the MAX to a variable does not improve the process :-(

 

But Thank you for you answer :-)

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous 

    There is a problem because the entire table is iterated for each row. One possible remedy:


    1. Add a couple of grouping indexes with Calculated Columns:

    TimeGroup1 = rounddown(DateForMA[Time] + 15/ 20,0)

TimeGroup2 = rounddown(DateForMA[Time] + 5/ 20,0)


    2. Instead of AVERAGE calculation, find the sum and then count of all that are in at least one of the 2 groupings, and then divide the sum by the count:

     

    MovingAverageMeasure2 = 
var cur_time = MAX(DataForMA[Time])
var sum_values = 
CALCULATE(
SUM(DataForMA[Value]);
ALLEXCEPT(DataForMA[TimeGroup1])
)
+
CALCULATE(
SUM(DataForMA[Value]);
ALLEXCEPT(DataForMA[TimeGroup2])
)
var count_values = 
CALCULATE(
COUNT(DataForMA[Value]);
ALLEXCEPT(DataForMA[TimeGroup1])
)
+
CALCULATE(
COUNT(DataForMA[Value]);
ALLEXCEPT(DataForMA[TimeGroup2])
)
return DIVIDE(sum_values, count_values)

    Hope this helps,

    Nathan

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous -

      Just realized this was wrong...

       

      The calculated columns are good. But for the calculation, you need to get the distinct list of relevant times and then do your original AverageX.

       

      Something like this:

       

      var cur_time = MAX(DataForMA[Time])
      var mytab = UNION(ALLEXCEPT(DataForMA; DataForMA[TimeGroup1]); ALLEXCEPT(DataForMA; DataForMA[TimeGroup2])
      var mytabdistinct = summarize(mytab;[Time];"Value";max([Value]))
      AVERAGEX( 
          [Value];
          FILTER(
              mytabdistinct ; 
              AND([Time] -10<= cur_time ; [Time] +10>= cur_time)
          )
      )

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        One more try:

        MovingAverageMeasure2 = 
        var cur_time = MAX(DataForMA[Time])
        var mytab = UNION(
            ALLEXCEPT(DataForMA; DataForMA[TimeGroup1]); 
            ALLEXCEPT(DataForMA; DataForMA[TimeGroup2])
        )
        var mytabdistinct = summarize(mytab;[Time];"Value";max([Value]))
        AVERAGEX( 
            FILTER(
                mytabdistinct ; 
                AND([Time] -10<= cur_time ; [Time] +10>= cur_time)
            );
            [Value]
        )