cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maibacherstr
Helper III
Helper III

Modify the LookUpValue function when Search_ColumnName1 contains duplicates

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

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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

@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!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors