Excel's MAX & MIN function functionality in DAX

[peejay]

Hi
I have a measure to calculate % Growth, but I find I'm getting some large results. I would like to limit the result to be between -999% and +999% in my visuals as these tend to be outliers.
In Excel I can do this using a combination of MIN and MAX (eg =MIN(999, MAX(-999, calculation)), however in DAX these functions operate differently.

Is it possible to achieve something like this in DAX, and if so how please?

Thanks in advance.

 

[gazpage]

Err, they pretty much do work like that. The aggregators either take a single column, or two expressions, and return the minimum.

MIN ( 999, [Revenue] ) should work fine.

 

[peejay]

Thanks Gazpage, but MAX & MIN only take a single argument according to the error message I got when I tried this...

Revenue % Growth YTD:=MIN(999, MAX(-999, DIVIDE([Revenue YTD] - [Revenue YTD LY], ABS([Revenue YTD LY]))))

I tried just MIN(999, [Revenue YTD LY]) just to further test it, but same outcome.

I was hoping to avoid having to calculate the growth value more than once (by having to use IF or SWITCH) or creating another measure but I think I'm stuck with adding another measure.
I've now used the following, which does the trick...

Rev % Growth YTD bounded:=SWITCH(TRUE(),
ABS([Revenue % Growth YTD]) < 9.99, [Revenue % Growth YTD],
[Revenue % Growth YTD] > 9.99, 9.99,
-9.99)

I've used the ABS function to first check if the % falls within -999% & 999% (as this will be the norm) before checking for the outliers.

Thanks once again for your speedy reply.

 

[gazpage]

https://msdn.microsoft.com/en-us/query-bi/dax/min-function-dax

Not really sure. See description of MIN and the two expression variant.

 

[peejay]

Thanks again gazpage.

I think it's the version of Microsoft Visual Studio I'm using (2010!). Hopefully our IT guys will get a more modern version installed sooner than later.