Lookup help

[ChrisPeall]

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	Date	DateName	Rate
Bob	01/01/2021	Bob01/01/2021	£1
John	01/01/2021	John01/01/2021	£1
Jess	01/01/2021	Jess01/01/2021	£1
Eva	01/01/2021	Eva01/01/2021	£2
Bob	02/01/2021	Bob02/01/2021	£1
John	02/01/2021	John02/01/2021	£1
Jess	02/01/2021	Jess02/01/2021	£1
Eva	02/01/2021	Eva02/01/2021	£2

Import

Name	Date	Datename	Assignment
Bob	01/01/2021	Bob01/01/2021	ASGN001
Bob	01/01/2021	Bob01/01/2021	ASGN002
Bob	02/01/2021	Bob02/01/2021	ASGN001
Jess	02/01/2021	Jess02/01/2021	ASGN005
Jess	01/01/2021	Jess01/01/2021	ASGN005

[amitchandak · Accepted solution]

@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

[HotChilli · Accepted solution]

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

[ChrisPeall]

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

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)

[ChrisPeall]

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 · Accepted solution]

@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

[ChrisPeall]

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.