Excel's MAX & MIN function functionality in DAX

peejay

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

Active Member
Joined
Apr 17, 2015
Messages
393
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
Joined
Jul 11, 2003
Messages
79
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.
 

peejay

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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top