Display name of measure (column) with highest value

[Anonymous]

I have an "employee" table with 10 measures. Each contains a value from 0.0 to 1.0.

I would like to display the titles (text) for the 3 highest measures on 3 separate cards. (1st place, 2nd place, 3rd place)

I know it's possible to do this with a nested IF() statement, but with 10 columns it gets very long. Is there a better way?

[Anonymous]

Hi @Anonymous ,

Could you please share the sample data and your expected output for my better understanding.

Best Regards,
Mail2inba4 

[Anonymous]

Hi @Anonymous ,

Could you please share the sample data and your expected output for my better understanding.

Best Regards,
@Anonymous 

[Anonymous]

The 10 items are measures, so they don't appear in a table.  Each measure has a value between 0.0 and 1.0.

Sample data:

Measure1: 0.5

Measure2: 0.4

Measure3: 0.3

Measure4: 0.2

Measure5: 0.1

Measure6: 0.9

Measure7: 0.8

Measure8: 0.7

Measure9: 0.6

Measure10: 0.0

Expected output text:

1st place card: "Measure6"

2nd place card: "Measure7"

3rd place card: "Measure8"

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following three measures to meet your requirement if the name of measure is constant :

BiggestValueOfMeasure = 
VAR t0 = {
    ( "Measure1", [Measure1] ),
    ( "Measure2", [Measure2] ),
    ( "Measure3", [Measure3] ),
    ( "Measure4", [Measure4] ),
    ( "Measure5", [Measure5] ),
    ( "Measure6", [Measure6] ),
    ( "Measure7", [Measure7] ),
    ( "Measure8", [Measure8] ),
    ( "Measure9", [Measure9] ),
    ( "Measure10", [Measure10] )
}
VAR t =
    ADDCOLUMNS ( t0, "Rank", RANKX ( t0, [Value2],, DESC,Dense ) )
RETURN
    MAXX ( FILTER ( t, [Rank] = 1 ), [Value1] )

SecondBigValue = 
VAR t0 = {
    ( "Measure1", [Measure1] ),
    ( "Measure2", [Measure2] ),
    ( "Measure3", [Measure3] ),
    ( "Measure4", [Measure4] ),
    ( "Measure5", [Measure5] ),
    ( "Measure6", [Measure6] ),
    ( "Measure7", [Measure7] ),
    ( "Measure8", [Measure8] ),
    ( "Measure9", [Measure9] ),
    ( "Measure10", [Measure10] )
}
VAR t =
    ADDCOLUMNS ( t0, "Rank", RANKX ( t0, [Value2],, DESC ,Dense) )
RETURN
    MAXX ( FILTER ( t, [Rank] = 2 ), [Value1] )

ThirdValueOfMeasure = 
VAR t0 = {
    ( "Measure1", [Measure1] ),
    ( "Measure2", [Measure2] ),
    ( "Measure3", [Measure3] ),
    ( "Measure4", [Measure4] ),
    ( "Measure5", [Measure5] ),
    ( "Measure6", [Measure6] ),
    ( "Measure7", [Measure7] ),
    ( "Measure8", [Measure8] ),
    ( "Measure9", [Measure9] ),
    ( "Measure10", [Measure10] )
}
VAR t =
    ADDCOLUMNS ( t0, "Rank", RANKX ( t0, [Value2],, DESC,Dense ) )
RETURN
    MAXX ( FILTER ( t, [Rank] = 3 ), [Value1] )

 

Or we can use the following measures with a What-If Parameter to get a dymanic card visual.

MeasureOfSelectRankOf = 
VAR t0 = {
    ( "Measure1", [Measure1] ),
    ( "Measure2", [Measure2] ),
    ( "Measure3", [Measure3] ),
    ( "Measure4", [Measure4] ),
    ( "Measure5", [Measure5] ),
    ( "Measure6", [Measure6] ),
    ( "Measure7", [Measure7] ),
    ( "Measure8", [Measure8] ),
    ( "Measure9", [Measure9] ),
    ( "Measure10", [Measure10] )
}
VAR t =
    ADDCOLUMNS ( t0, "Rank", RANKX ( t0, [Value2],, DESC,Dense ) )
RETURN
    MAXX ( FILTER ( t, [Rank] = [SelectMeasureRank Value] ), [Value1] )

Best regards,

[Anonymous]

Hi @Anonymous ,

As your data is a measure, we can go with nestedif or SWITCH().

Best Regards,
@Mail2inba4