Table with duplicate keys - want to return 1 value in 1 column based on another colum

[patrick3]

Hi community, 

Not quite sure how best to describe it, but a table should help:

UserKey	Value	Category	Return of Formula
1	20	Blue Widgets	Red Widgets
2	30	Red Widgets	Red Widgets
3	40	Green Widgets	Green Widgets
4	50	Blue Widgets	Blue Widgets
1	30	Green Widgets	Red Widgets
5	40	Green Widgets	Green Widgets
6	50	Blue Widgets	Blue Widgets
1	40	Red Widgets	Red Widgets

As you can see, the UserKey "1" appears 3 times, with 3 different values and 3 different categories.

I'm looking for a way to write a dax formula that checks all occurences of the UserKey, then returns the category with the highest value in the new column.

In the example, I want the new column to have "Red Widgets" because it has a value of 40 in the last row.

Hope that makes sense and someone can help me out.

Thanks,

Patrick

[jdbuchanan71 · Accepted solution]

Hello @patrick3 

Give this a try.

Return of Formula =
VAR _UserKey = 'Table'[UserKey]
VAR _MaxValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        ALL ( 'Table' ),
        'Table'[UserKey] = _UserKey
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Category] ),
        ALL ( 'Table' ),
        'Table'[Value] = _MaxValue,
        'Table'[UserKey] = _UserKey
    )

[patrick3]

@jdbuchanan71  that's perfect, thanks a lot!