topic Re: Optimize MAXX in DAX Commands and Tips

Optimize MAXX

Anonymous — Wed, 08 Jul 2020 16:55:30 GMT

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

Re: Optimize MAXX

lbendlin — Wed, 08 Jul 2020 17:16:25 GMT

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

Re: Optimize MAXX

Anonymous — Wed, 08 Jul 2020 17:47:56 GMT

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

Re: Optimize MAXX

lbendlin — Wed, 08 Jul 2020 23:52:54 GMT

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

Re: Optimize MAXX

Anonymous — Thu, 09 Jul 2020 15:58:41 GMT

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?

Re: Optimize MAXX

AntrikshSharma — Thu, 09 Jul 2020 16:20:36 GMT

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

Re: Optimize MAXX

Anonymous — Thu, 09 Jul 2020 17:04:53 GMT

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

Re: Optimize MAXX

Anonymous — Fri, 10 Jul 2020 05:44:11 GMT

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