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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
ChrisPeall
New Member

Lookup help

Hi,

 

I have two tables as per below. What I'm trying to do is assign a new column in the 'Import' table that looks up the DateName in that table, looks up the corresponding DateName in the 'Rate Sheet' table and applies that person's rate into the new column. I concatonated the Date and Name to create a unique field in the Rate Sheet so there is a one to many relationship between the Rate Sheet and Import tables. 

 

I've tried this but it doesn't work: 

= LOOKUPVALUE('Rate Sheet'[Rate],'Rate Sheet'[DateName],'Import'[DateName])
 
I'm a complete novice to using DAX as you can probably tell. Is lookup even the right command to use?

 

Rate Sheet

Name DateDateNameRate
Bob01/01/2021Bob01/01/2021£1
John01/01/2021John01/01/2021£1
Jess01/01/2021Jess01/01/2021£1
Eva01/01/2021Eva01/01/2021£2
Bob02/01/2021Bob02/01/2021£1
John02/01/2021John02/01/2021£1
Jess02/01/2021Jess02/01/2021£1
Eva02/01/2021Eva02/01/2021£2

 

Import

NameDateDatenameAssignment
Bob01/01/2021Bob01/01/2021ASGN001
Bob01/01/2021Bob01/01/2021ASGN002
Bob02/01/2021Bob02/01/2021ASGN001
Jess02/01/2021Jess02/01/2021ASGN005
Jess01/01/2021Jess01/01/2021ASGN005
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@ChrisPeall , seem file a new column in invoice

 

or try like

 

a new column = MAXX(filter('Rate Sheet', 'Rate Sheet'[DateName] ='Import'[DateName]),'Rate Sheet'[Rate])

 

 


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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

HotChilli
Super User
Super User

Are you creating a measure or a calculated column? I thought you wanted a calculated column

View solution in original post

6 REPLIES 6
HotChilli
Super User
Super User

Are you creating a measure or a calculated column? I thought you wanted a calculated column

.. and that is the problem with picking something up Monday morning that you were looking into Friday evening. You're absolutely right, I should have been creating a calcualted column, and in my haste to continue hit new measure instead! Thank you!

HotChilli
Super User
Super User

2 points:

What's not working with the LOOKUPVALUE dax provided?

Also, the test sample doesn't look great because 1) the LOOKUP works 2) answers are all £1 (so not much of a test)

Hi, thanks for taking a look. 

 

In answer to yuor questions:

1. I get the following error message with both the LookupValue and the suggestion below: "A single value for column 'Datename' in table 'Import' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as a min, max, count, or sum to get a single result."

2. Fair point, I just knocked it together to scramble the real data. 

 

I can't get as far as seeing any results, even if they were all £1 because of the error message. 

amitchandak
Super User
Super User

@ChrisPeall , seem file a new column in invoice

 

or try like

 

a new column = MAXX(filter('Rate Sheet', 'Rate Sheet'[DateName] ='Import'[DateName]),'Rate Sheet'[Rate])

 

 


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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks for taking the time to reply. I tried it and get the error:

 

A single value for column 'Datename' in table 'Import' cannot be determined. This can happen when a measure formula refers to a colun that contains many values without specifying an aggregation such as a min, max, count, or sum to get a single result.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.