Forum Discussion
Advocate IIIHow 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]))
Error: "A max function was used in a true / false expression ..."
Thank you,
LRFeltz
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
3 Replies
chrisuResponsive Resident
I run into this a lot because I always think MAX should work the way you have it as well. What you need to add is a statement saying what should equal MAX, like this:
CALCULATE(SUM(TBL[TOTAL]);TBL[MÊS]=MAX(TBL[MÊS]))
That will turn it into the true/false expression necessary by evaluating whether TBL[MÊS] equals the max TBL[MÊS].
- OwenAuger
Super User
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