LOOKUPVALUE function not working, and it SHOULD be working
Question
Thursday, November 28, 2013 3:53 PM
Hi there,
I am using a simple LOOKUPVALUE function to return a price from another table in PowerPivot v11.0.3000.0 on Excel 2010 x64. I have already used the identical formula to return three other prices in the same table, so there shouldn't be a problem with the formula or the tables. The only difference between the formulas is the Result_ColumnName that I want to return. This is inexplicable.
Here is the PowerPivot formula:
=LOOKUPVALUE(CI_Fixed[Price4], CI_Fixed[Date-Time], [DATE_TIME])
The formula simply looks up the price with the corresponding date-time from the "CI_Fixed" table. The date-times in both tables are formatted in the "1/1/2013 1:00:00 AM" format.
The error is:
"Calculation error in column 'Main Table'[]: A table of multiple values was supplied where a single value was expected."
As I said, I have already used the identical function to return other prices from the "CI_Fixed" table to the "Main Table".
Thanks very much in advance for your help! Please let me know if you require more information about the problem. This seems like a bug to me.
Sean
All replies (6)
Friday, November 29, 2013 4:58 PM âś…Answered | 1 vote
Here is a quick illustration of what may be happening:
You most likely have one or more rows with the same value in the [DATE_TIME] column and different values in the [Price4] column. Let me draw your attention to the two rows highlighted in yellow. In these rows, the [Price1] column has only one unique or distinct value i.e. '27.98'. The same is true for the [Price2] and [Price3] columns. However, the [Price4] column has 2 different values across the same 2 rows i.e. '31.53' and '32.35'. Since the LOOKUPVALUE function is only expecting a single column with one unique or distinct value, the [Price4] column is triggering the error.
If you confirm that this is what is happening and that it is normal for the 'CI_FIXED' table to have duplicate values in the [DATE_TIME] column, then you will need to change the formula and potentially the model to accommodate this. If the table isn't meant to hold duplicates in this column, then you can remove the duplicates from the table's data source to fix the issue.
Friday, November 29, 2013 1:27 PM
Hi seanrez,
Are you able to provide some sample data (i.e. for 'Main Table' and 'CI_Fixed') so that we can try and reproduce this?
Thanks
Michael
Friday, November 29, 2013 3:23 PM
How's this? Thanks a lot, Michael. What I am doing is very similar to a VLOOKUP in Excel.
Sean
CI_FIXED
Date-Time |
Price4 |
1/1/13 0:00 |
27.98 |
1/1/13 1:00 |
3.37 |
1/1/13 2:00 |
6.06 |
1/1/13 3:00 |
22.28 |
1/1/13 4:00 |
32.04 |
1/1/13 5:00 |
12.07 |
Main Table
DATE_TIME |
Price not working |
1/1/13 0:00 |
=LOOKUPVALUE(CI_Fixed[Price4], CI_Fixed[Date-Time], [DATE_TIME]) |
1/1/13 1:00 |
|
1/1/13 2:00 |
|
1/1/13 3:00 |
|
1/1/13 4:00 |
|
1/1/13 5:00 |
Friday, November 29, 2013 4:02 PM | 1 vote
Hi Sean,
The calculation is working for me when using the sample data above. However, I can re-produce the error by introducing a duplicate row in the CI_FIXED[Date-Time] column. I changed "1/1/13 1:00" to "1/1/13 0:00" so that there were two rows in CI_FIXED with the value "1/1/13 0:00". Can you double check that there are no rows in your CI_FIXED table with the same value in the [Date-Time] column?
Result 1 - The expected behaviour when using the data unchanged:
Result 2 - An error message after introducing a duplicate value into the CI_FIXED[Date-Time] column:
Friday, November 29, 2013 5:26 PM
Michael, you are absolutely right! There was one duplicate date-time in my data, but I didn't notice it because for most of my calculated LOOKUPVALUE columns, the prices were identical for both duplicate hours. But for the one with the error, the price was a few pennies different in the two duplicate date-times!
Wow, you've really gone above and beyond for me here. I really, really appreciate it! I almost can't believe you guessed the right answer without even seeing my whole data-set. You are obviously very good at what you do!
Thanks again. This forum rocks! :D
Sean
Sean McCarthy
Friday, November 29, 2013 5:30 PM
Glad I could help :-)