Modify the LookUpValue function when Search_ColumnName1 contains duplicates

[maibacherstr]

Hello Community,

I have a =lookupvalue formula running in my DAX, and all was good. But due to a project requirement, today I had to update my query filter which caused my dataset to increase in size. This added data into to the numerical column that is/was being used for the "Search_ColumnName1" argument.

LOOKUPVALUE(Result_ColumnName, Search_ColumnName1, Search_Value1)

The issue arises because by adding data into the column used in the formula, this created duplicates which return an error for the lookupvalue function.

I have another column (titled ProjectName, where my duplicates belong to either Project A or Project B) which makes this easy to distinguish which record I intend to locate. The ProjectName value of the row I'm starting with is the same ProjectName I'm looking for.

Is there a way to modify my =lookupvalue function to distinguish among these duplicates?

I just hope I've asked the question right. MANY THANKS

[amitchandak]

@maibacherstr ,

you can try like

in same table

maxx(filter(Table, Table[Search_ColumnName1] = earlier(Table[Search_Value1]) , [Result_ColumnName])

for across table refer

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[maibacherstr]

@amitchandak I'm a fan of your work from many solutions past; thank you for your time/energy & reply.

I'm reading your formula as a new DAX column. Unfortunately I haven't had the opportunity to focus on building this since you posted it. It may happen over the weekend, and if so I just wanted to check to before then to acknowledge and say thanks.

Have a great weekend!

[Anonymous · Accepted solution]

Hi @maibacherstr,

I think you can add more search columns to the lookupvalue function to prevent the duplicate value and set the 'alternate Result' option with aggregate function to handle the excepted duplicate results.

formula =
LOOKUPVALUE (
    Table2[Result],
    Table2[SearchColumn], Table1[Column],
    Table2[ProjectName], Table1[ProjectName],
    MAX ( Table2[Result] )
)

LOOKUPVALUE function (DAX) - DAX | Microsoft Docs

Regards,

Xiaoxin Sheng