# Excel's MAX & MIN function functionality in DAX

#### peejay

##### Board Regular
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?

#### gazpage

##### Active Member
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

##### Board Regular
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.

#### peejay

##### Board Regular
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.

1,082,151
Messages
5,363,449
Members
400,737
Latest member
vipamuk

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...