Unexpected error in formula - max for each group

[Anonymous]

Hello,

I am trying to achieve effect in table below. I already used this measure in another table, however this time I got an unexpected error and I am not sure how to solve it, I would appretiate a ton any idea.

desired outcome = LOOKUPVALUE(
    'data'[name],
    'data'[value],CALCULATE(MAX('data'[value]),ALLEXCEPT('data','data'[id])),
    'data'[id],SELECTEDVALUE('data'[id])
    )

Error: A table of multiple values was supplied where a single value was expected.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a calculated column or a measure

Column =
VAR max_value =
    CALCULATE ( MAX ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[name] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[id] ), 'Table'[value] = max_value )
    )


Measure =
VAR max_value =
    CALCULATE ( MAX ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[name] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[id] ), 'Table'[value] = max_value )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.