Excel's MAX & MIN function functionality in DAX

peejay

Board Regular
Joined
Jul 11, 2003
Messages
83
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top