A more elegant solution than an If statement

[JEB85]

Hi Guys,

Is there a way to return the measure name in a DAX formula that produces the maximum value?

For example, lets say I have 3 measures that yield:

Measure 1: 25
Measure 2: 32
Measure 3: 28

I would like to create a formula which would return ‘Measure 2’ in this instance since it is the largest value.

I know this is achievable through an if statement but I wondered if there was a more elegant solution since I have lots of conditions in reality

Thanks

 

[Mackers]

Hi

I might be misinterpreting the question, but if you have those three as strings in A2:A4 and the numbers are always 1 or 2 digits, you can use as an array function (ctrl+shift+enter):

=INDEX(A2:A4, MATCH(MAX(--RIGHT(A2:A4,2)), --RIGHT(A2:A4, 2), 0))

If Measure1, Measure2 and Measure3 are in column A and the numbers are in column B it is a lot easier (no need for an array function):

=INDEX(A2:A4, MATCH(MAX(B2:B4), B2:B4, 0))

If the number varies more in length there are fancier ways of chopping this bit out, but it sounds like your main question was about how to extract the largest value.

 

[JEB85]

Hi,

thanks but the measures are dax measures. I'm wanting to return the name of the dax measure that yields the largest value.

you' re right, I could go down the route you have suggested but I was hoping to calculate the result through another dax measure.

thanks

 

[scottsen]

Other than using SWITCH() instead of IF() .... I can't think of a way off hand. I can maybe possible imagine a different solution depending on what all those measures are doing... but I doubt it