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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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