Forum Discussion

Cadams's avatar
Cadams
Microsoft Employee
4 years ago
Solved

Lookupvalue - Max value with duplicate max values

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!

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    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

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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