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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

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!

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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