Optimize MAXX

[niveekiwi]

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

[harshnathani]

Hi @niveekiwi ,

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

[lbendlin]

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]

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

[niveekiwi]

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]

3M is not a big number, can share your file?

[niveekiwi]

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

[niveekiwi]

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