Setting a Maximum value for a DAX measure

[DickyMoo]

Hi everyone,

Is there a way to set a MAX value for a measure? I.e. the equivalent of Excel's =MIN(A1*A2,100).

I have a list of percentage variances against budget and some are massive eg 21,000%, it would be nice to show this as 100%, so I can use conditional formatting (in PowerPivot).

I suspect I can do an IF('measure' > 100, ">100%", <measure>), but this might cause performance issues as I'd be doing calcs twice.

Thanks for any advice you may have.

(fyi I have also posted this on Excelguru.ca)

Richard

 

[Ozeroth]

Hi Richard,

Current versions of DAX (e.g. Excel 2016 or Power BI Desktop) include MIN and MAX functions which can operate on two scalar expressions, same as Excel.
So you can write expressions like MIN( [Measure], 100 )

Unfortunately, earlier versions of DAX (e.g. Excel 2013/2010) don't have this feature (MIN and MAX can only return min or max of a column), so you are stuck doing the calculation twice within an IF as you have described.

 

[DickyMoo]

Ok, thanks Ozeroth, I appreciate your response.

I am using PowerPivot in 2010 but upgrading to 2016 in a couple of months so will make do until then.

Richard