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

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

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

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

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
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.

Top Kudoed Authors