Setting a Maximum value for a DAX measure

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

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.

Thanks Brian.

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

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%") )

Very nice, I didn't know that one. Thanks Ted.

Ted Murphy said:

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%") )

Click to expand...

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.