Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Optimize MAXX

How can I find the maximum of previous rows with rescpect to the current row? currently I get a memory error when I run this DAX.    maxbi:= MAXX( FILTER( Allexcept('TI'; 'TI'[Assignment group]; '...
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

Put the max(date) in a variable to compute it only once

 

maxbi:= 
var md = MAX([date])
return MAXX(
    FILTER( Allexcept('TI'; 'TI'[Assignment group]; 'TI'[Type])
           ; [date] <= md )
; 'TI'[Stock])
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

yes, that filter is still there. But now you only compute the upper limit once, instead of for every row.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for the input. That did not help. I have over 3 million rows. This formula iterates previous rows to find the maximum value of the measure. Is there another way to formulate?

    • AntrikshSharma's avatar
      AntrikshSharma
      Icon for Community Champion rankCommunity Champion
      6 years ago
      3M is not a big number, can share your file?
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I cannot share the data set as it is confidential. can it be because I refer TI[stock] as a measure?