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]; 'TI'[Type]); [date] <= MAX([date])); 'TI'[Stock])

7 Replies

  • 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])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I mean by that to calculate dates previous to the selected row date.

    • 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Try this measure

     

    maxbi =
VAR md =
    MAX ( 'TI'[Date] )
RETURN
    CALCULATE (
        MAX ( 'TI'[Date] ),
        FILTER (
            ALL ( 'TI' ),
            'TI'[Assignment group]
                = MAX ( 'TI'[Assignment group] )
                && 'TI'[Date] < md
        )
    )

     

     

    Regards,

    Harsh Nathani