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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Advocate I
Advocate I

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)


Community Champion
Community Champion

Hi @fjmocke


Could you please Try this alternative


    FIRSTNONBLANK ( Shifts[TruckOperatorName], 1 ),
    FILTER ( ALL ( Shifts ), Shifts[UniqueID] = FuelData[UniqueShiftID] )


Please try my custom visuals

View solution in original post


Hi @Zubair_Muhammad , Thanks for your response! I haven't had a chance to try your suggestion as, before I saw it, I ended up looking through the data and saw a couple of the machines were duplicated (but with the same number of operators in the duplicate records), so I changed the formula to this so it couldn't return multiple values:


var thisMachine='toptessa_wp541 cm_Job_Card'[Machine]
var thisCompany='toptessa_wp541 cm_Job_Card'[Company]
var Asset_ID=
CALCULATE(maxx('dbma76 cm_Scheduled_Maintenance_Asset',[ID]),filter('dbma76 cm_Scheduled_Maintenance_Asset',[Company]=thisCompany&&[Asset_Name]=thisMachine))
LOOKUPVALUE('dbma76 cm_Scheduled_Maintenance_Asset'[Operators],'dbma76 cm_Scheduled_Maintenance_Asset'[ID],Asset_ID)
Not applicable

@Zubair_Muhammad , the solution works out very well in most of the cases....but there is a minor issue that am facing while using this....Part is the column that i have in main table, Yes/No & Part status are the columns that am doing a lookup from another table...the green highlighted ones work perfectly but there are some cases where am getting wrong results highlighted in red....the reason is that those parts are having more than 1 value in the table from where am trying to do a lookup...the Part status column is giving me correct results but the issue is with Yes/No as those parts have an entry with "No" & "Yes" ....can you please help me with this issue



This helped me, even with multiple filters.

Not applicable

Thank you, Zubair.

Is there any way we can capture all the values for a particular ID. That is , for this example, can we capture the name of all the [TruckOperatorName] from Shifts table, if there are multiple and different values of [TruckOperatorName] for a particular value of [ShiftID]?

Or may be return a new table which would capture the name of all the [TruckOperatorName] that is there for a [ShiftID]?

I had the same issue and tried this solution and worked... I just would like to understand why, what is the measure doing that works?

Hi Zubair,


I have tried the same kind of foluma in PowerPivot, but it not working, could you please help me ?

I have similar type of issue and resolved the same with your solution. 

But here i got different problem... I have same unique id multiple times. in this case i have look and sum the values then put it in result column.


Culd you please help me in it.

Thank you. This solved a variant problem for me.

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.

HI @fjmocke


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


Using FirstNonBlank/ LastNonBlank bypasses it




Please try my custom visuals

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.  



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

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




Please try my custom visuals

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


PreviousMonthMetricValue = 




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



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

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors