cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fjmocke Frequent Visitor
Frequent Visitor

LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected"

I'm trying to use the LOOKUPVALUE function to lookup values from Table 2 in Table 1.  It is supposed to be a simple venture but for some reason one of the columns won't work.  I get the following error:

 

"A table of multiple values was supplied where a single value was expected"

 

Column UniqueShiftID in Table1 and UniqueID in Table2 are used for referencing the rows.

 

In Table1, columns Crew and Shift work with the LOOKUPVALUE function.  Taking the exact same code, and referncing TruckOperatorName in Table2, gives the error.

 

Any idea why this would happen?

 

 

TABLE 1 (FuelData)

 

 

TABLE 2 (Shifts)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&qu

Hi @fjmocke

 

Could you please Try this alternative

 

Operator=
CALCULATE (
    FIRSTNONBLANK ( Shifts[TruckOperatorName], 1 ),
    FILTER ( ALL ( Shifts ), Shifts[UniqueID] = FuelData[UniqueShiftID] )
)
10 REPLIES 10
Super User
Super User

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&qu

Hi @fjmocke

 

Could you please Try this alternative

 

Operator=
CALCULATE (
    FIRSTNONBLANK ( Shifts[TruckOperatorName], 1 ),
    FILTER ( ALL ( Shifts ), Shifts[UniqueID] = FuelData[UniqueShiftID] )
)
fjmocke Frequent Visitor
Frequent Visitor

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

Thanks it did the trick.  Any idea why the LOOKUPVALUE function is not working?  I don't understand why it is working for the other columns but not the Operator one.

Highlighted
Super User
Super User

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

HI @fjmocke

 

There must be some duplicate Shift[UniqueIDs] in your table

 

Using FirstNonBlank/ LastNonBlank bypasses it

 

 

Super User
Super User

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

fjmocke Frequent Visitor
Frequent Visitor

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

I made sure there are no duplicate UniqueID by doing GROUP BY in my SQL Query.  If that was the case the other columns also won't work.  

Super User
Super User

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

@fjmocke

 

Could you check for duplicates by adding this calculated column in SHIFTS table?



Duplicate Check =
CALCULATE ( COUNT ( Shifts[UniqueID] ), ALLEXCEPT ( shifts, Shifts[UniqueID] ) )

 

 

fjmocke Frequent Visitor
Frequent Visitor

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

You are correct, there are duplicates.  Not sure how I missed that.  Thanks will fix it.  You really helped me out here

atul9063 Member
Member

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

@Zubair_Muhammad I m getting same issue when i m using below lookup formula 

 

PreviousMonthMetricValue = 
LOOKUPVALUE(vw_Engmt_Metrices[MetricValue],vw_Engmt_Metrices[dt],DATEADD(vw_Engmt_Metrices[dt],-1,MONTH))

 

image.png

 

I want below output..Let me know what i m doing wrong here

 

image.png

wulingmountain Frequent Visitor
Frequent Visitor

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

Thank you. This solved a variant problem for me.