Forum Discussion
Solved
How to use the MAX function as a filter in calculation?
Hello, I would like some help ... How to use the MAX function as a filter in calculation? Example: the function below returns the error: CALCULATE(SUM(TBL[TOTAL]);MAX(TBL[MÊS])) Er...
I don't believe MAX (or any aggregation function) can be used directly within a CALCULATE boolean filter argument.
I find FIRSTNONBLANK/LASTNONBLANK useful in this situation.
(http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/)
For example:
= CALCULATE ( SUM ( TBL[TOTAL] ); LASTNONBLANK ( TBL[MÊS]; 0 ) )
You could also use variables to get closer to your suggested syntax:
= VAR MaxMÊS = MAX ( TBL[MÊS] ) RETURN CALCULATE ( SUM ( TBL[TOTAL] ); TBL[MÊS] = MaxMÊS )
Regards,
Owen
I don't believe MAX (or any aggregation function) can be used directly within a CALCULATE boolean filter argument.
I find FIRSTNONBLANK/LASTNONBLANK useful in this situation.
(http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/)
For example:
= CALCULATE ( SUM ( TBL[TOTAL] ); LASTNONBLANK ( TBL[MÊS]; 0 ) )
You could also use variables to get closer to your suggested syntax:
= VAR MaxMÊS = MAX ( TBL[MÊS] ) RETURN CALCULATE ( SUM ( TBL[TOTAL] ); TBL[MÊS] = MaxMÊS )
Regards,
Owen
