topic Re: Lookupvalue - Max value with duplicate max values in DAX Commands and Tips

Lookupvalue - Max value with duplicate max values

Cadams — Fri, 25 Mar 2022 22:09:52 GMT

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!

Re: Lookupvalue - Max value with duplicate max values

tamerj1 — Sat, 26 Mar 2022 03:09:55 GMT

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

Re: Lookupvalue - Max value with duplicate max values

Cadams — Sat, 26 Mar 2022 22:56:33 GMT

That worked tamerj1 , thank you so much !!