Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

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
Anonymous
Not applicable

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

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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
Not applicable

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

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.