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 value. So you could test that with a simple table with two columns, 1 for indexes, 1 for values. What i want to perform is a MA  with 10 indexes back and 10 forward. So i use this formula : 

MovingAverageMeasure2 = 

    AVERAGEX(
    FILTER(ALL(DataForMA);
    AND(
        DataForMA[Time] -10<= MAX(DataForMA[Time]); 
        DataForMA[Time] +10>= MAX(DataForMA[Time])));
    DataForMA[Value])

 

This works but i am badly surprised of the poor performance of that calculation. Here, i ve testes it on a table that has only 4000 rows but if i increase that number to 20000 for example, refresh time then become terrible. (we talk about minutes)

I ve tried the same calculations in T*****, i ve got no problems, refresh is nearly immediate.

 

The only solution i ve found is to precalculate the MA Points in a new Dax column and the plot the value.

(However, Preloading time is still long and you can't for example modify the number of indexes back or forward with a parameter)

 

Mov_Average_Column= 
var DynamicTable =
CALCULATETABLE(
    DataForMA; 
    ALL(DataForMA);
    AND(
        DataForMA[Time] -10<= EARLIER(DataForMA[Time]); 
        DataForMA[Time] +10>= EARLIER(DataForMA[Time])))
return 
AVERAGEX(DynamicTable; DataForMA[Value])

So, could you tell me if i am missing something? Should I try other formulas ?

 

Thanks in advance for your answers :-) 

 

 

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]
)

     

     

15 Replies

  • 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