PDA

View Full Version : Setting a Maximum value for a DAX measure



DickyMoo
2016-03-07, 05:00 PM
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%. Would be nice to show this as something like >100%, or similar.

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 using PowerPivot in Excel 2010)

Richard

Brian Smith
2016-04-13, 04:12 AM
It appears that it is no problem for a measure to return text when it meets a condition just like in excel. I was not able to make a measure where it would return a numerical value in one condition and text in another. There might be a way, but it seems like that would create some potential problems. Example with the text attached.

DickyMoo
2016-04-13, 12:43 PM
Thanks Brian.

I think I'll set anything over 100% to 100% and let users know, rather than complicate things too much.

Ted Murphy
2016-04-14, 12:30 PM
By adding a FORMAT function you can show the Actual % where it is less than your cutoff (100% in this case)

IF (Table1[BudgetToActual]>1,">100%",FORMAT([BudgetToActual],"0.00%") )

DickyMoo
2016-04-14, 04:56 PM
Very nice, I didn't know that one. Thanks Ted.

Brian Smith
2016-04-15, 05:15 AM
By adding a FORMAT function you can show the Actual % where it is less than your cutoff (100% in this case)

IF (Table1[BudgetToActual]>1,">100%",FORMAT([BudgetToActual],"0.00%") )

Nicely played :llama:

I was going to put in some clapping hands...but there's a llama. So I went with the llama. I stand by it.