Forum Discussion
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:
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 |
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=czNHt7UXIe8Are you creating a measure or a calculated column? I thought you wanted a calculated column
6 Replies
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=czNHt7UXIe8Thanks 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.
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!
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.
