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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
droupa
Frequent Visitor

lookupvalue - "A Table of Multiple values was supplied where a single value was expected"

Hi,

 

I have a Budget table that has a budgeted amount per month of the year.

 

I am trying to lookup a budget value (table: Budget) for a month to another date table (table: Daily Targets), to calculate a daily budget value. (My budget table only has monthly targets, but I need to obtain daily targets by dividing this value by the number of days)

 

In my Budget table, I created a column to work out the number of days for that month using: 

DAYS IN MONTH = EOMONTH([DATE],0)-[Date]+1
 
I also created a "Month and Year" text Column using:
Month and Year = concatenate(month(Budget[Date]),year(Budget[Date]) )
 
The Budget table therefore has a unique reference for each row for month and year.
 
In my Daily Target table, I created a column for Month and Year the same way:
Month and Year = concatenate(month('Daily Targets'[Date]),year('Daily Targets'[Date]) )
 
Therefore, each day has a month and year that it relates to; All days in the same month will have the same reference.
 
I want to lookup a column from my Budget Table using the Month and Year, using:
Daily Target = lookupvalue(Budget[Revenue (Budget)],Budget[Month and Year],'Daily Targets'[Month and Year])
 
I receive the error: "A table of multiple values was supplied where a single value was expected"
 
I dont understand why, because the Budget table has unique values; Why cant the formula just return the column value using the unique reference?
 
 
 
 
 
 
1 ACCEPTED SOLUTION

your month year filed in the budget table is not unique because there are 4 entries for every Month one fore each of your Propertys 

 

property.png

 

if you have the Propert in the Daily targets then concat all thre month year and propert to create a unique ref

if you do not have this filed then you need to find another way to make a uniqe ref





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
AnthonyTilley
Solution Sage
Solution Sage

Can you provide a PBIX file so that i can review the data,

 

this error would imply that your unique refrence colunm of month year is not unique 

 

if you upload a file i can review this and try an find the error 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@AnthonyTilley Sorry, I am quite new; Can you tell me the steps to upload the PBIX file and I will do so?

 

Thanks!

ypu will need to save the PBIX file to a cloud storage such as onedrive, google drive or drop box and then share the file to public with a link and then post the link in this thread

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ok. here it is: Sales Report

your month year filed in the budget table is not unique because there are 4 entries for every Month one fore each of your Propertys 

 

property.png

 

if you have the Propert in the Daily targets then concat all thre month year and propert to create a unique ref

if you do not have this filed then you need to find another way to make a uniqe ref





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ah! makes sense now. thanks!

your welcome

 

If there is nothing further please mark the topic as solved





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Do you perhaps have a quick way for me to edit my Daily Target Table so that I create 4 records of each row, and include a new column stating the lodge, so that I can create my unique reference?

ill take a look now 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




okay i have created a table that is a distinct list of proporties in your budget table 

 

i have then changed you Daily targets table to be a combine of your calander and this table to create a row for each merged item in the table 

 

i have then changed your concat formulas to give a unique refrence for the month year colunm and changed the relationship 

 

i have then fixed your lookup colunm in the daily targets table 

 

hope this helps 

 

pbix file





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks Anthony! This has really helped.

I added a divide function to my lookup formula, and now I have blanks in my DAILY TARGET Column in my DAILY TARGETS Table, and I am not seeing any logical reason why? Would you know?

 

Sales Report 2

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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