Lookupvalue - Max value with duplicate max values

[Cadams]

Good evening,

I'm wondering whether anyone can help. I'm trying to insert a new column in my table with a dax formula that returns the Primary Product based on the ID and max Value (see screenshot below with example data on Excel).

I'm currently using 'Lookupvalue' formula:

=lookupvalue ([product],[value],max([value]),[id],filter([table1],[id]=earlier([id])))

However, I keep getting the error (see screenshot below), as DAX can't figure out which max value to take. 

What I'd like to do is just take either max duplicated primary product (it doesn't matter which), so long as the formula doesn't return an error.

Can anyone figure out a way to do this, I'd really appreciate it!

[Cadams]

That worked @tamerj1 , thank you so much !!

[tamerj1 · Accepted solution]

Hi @Cadams 
You can use

New Column =
VAR CurrentIDTable =
    CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[ID] ) )
VAR MaxValue =
    MAXX ( CurrentIDTable, Table1[Value] )
VAR MaxValueProduct =
    CALCULATE ( MAX ( Table1[Product] ), Table1[Value] = MaxValue )
RETURN
    MaxValueProduct