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

[fjmocke]

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)

[JenWilson]

Thank you for the suggestion to use the FIRSTNONBLANK! I was trying to use the LOOKUP as well and not having any luck getting the results that I was looking for. Relationship between Table1 and Table2 are Many to Many. I used the FIRSTNONBLANK with multiple filters to narrow my results further. 

CALCULATE(

    FIRSTNONBLANK(Table1[QtyOf],1),

    FILTER(ALL(Table1),Table1[monum] = Table2[Order#] &&

    Table1[QtyOf] = Table2[qtyof] &&

    Table1[Operator] = Table2[EmployeeName]))

[dianalucia_mtz]

Hello! I had the same problem and I solved it with this function, I just came across with some cases where I get blanks where I shouldn't. This is my result table with the following function:

jobsite_url =
CALCULATE(
FIRSTNONBLANK(data_Jobsite_accountid[url entity],1),
FILTER(ALL(data_Jobsite_accountid),data_Jobsite_accountid[cmx_jobsitecode]='Listado Sucursales'[cmx_jobsiteid])
)

In the source table I do have the data for the 65000107 cmx-jobsiteid

 

 

What is going on? What can I do to solve this?

[Anonymous]

I'm having the same issue. Did you figure it out?

[Anonymous]

Hello everyone, I was hoping you could help me out. I was trying to do a look up function, but I was getting an error because it has duplicate values. I can across this so I figured I would try this Calculate but it didn’t return what I was looking for, can someone help me?

I wanted to take the value from  'FedEx Query'[Shipper Reference] and match it with the same number in 'DLX Report Query'[PALLET_ID] so that I could get the Dealer code value from 'DLX Report Query'[Dealer Code] which has duplicates, but it can just grab the first one.

I originally tried this and got an error for multiple values:  Dealer Code/DLX = LOOKUPVALUE('DLX Report Query'[Dealer Code], 'DLX Report Query'[PALLET_ID], 'FedEx Query'[Shipper Reference])

The I tried this and could get it to work:  Dealer Code DLX 3 = CALCULATE(FIRSTNONBLANKVALUE('DLX Report Query'[Dealer Code],1),FILTER(ALL('DLX Report Query'), 'DLX Report Query'[PALLET_ID] ='FedEx Query'[Shipper Reference]))CALCULATELOOKUPVALUE Error

[Zubair_Muhammad · Accepted solution]

Hi @fjmocke

Could you please Try this alternative

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

[vynguyen]

thank you!!!!!!!!!!!!!!!!!

[Amy_Qc]

@Zubair_Muhammad it's not working for me!!:(((( i have many Ids

Amy

[jassimkhan]

It works.

saved lot of time for me. Thanks 🙂

[benmcmaster77]

This worked for me. Especially the use of FIRSTNONBLANK - very helpful!

[Anonymous]

Hello, I am trying this query but not helping 

 This data is coming from multiple sources and the dashboard created needs to have distinct BOL/BILL visible.

[Kikki09]

Hi! Did you ever get a solution for this? I am facing the same struggle. Thanks!

[SilviaM]

It worked for me!!! thanks a lot for the solution!

[Muthu-Raja]

Thanks for solution its working fine but can you please explain why the lookup dax function not working sometimes, thank god there is some alternate way, but why lookup not working 

[LiziM]

I also need help with this please. I am  trying to pull [Operators] (conicidentally, but mine is the NUMBER of operators) from one table into another table, using the machine/ asset name.

In the screenshot below, the commented out formula is the one that yielded the "Multiple values supplied when one was expected" error. The non-commented out formula was my attempt at using this solution, which has yielded another error (shown in screenshot). I think the issue may be my "expression" value in the "firstnonblank" formula. Thank you in advance!! Let me know if you require more info, although I think looking at the formulae explains quite well.

[shubh_2492]

Use double ampersand instead.

For condition AND  = &&

For condition OR = ||

Or 

You can use method AND(condition1, condition2)

[Zubair_Muhammad]

Hello

Just add
&""
to the number to convert to string. And I think the formula will work

[LiziM]

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:

Operators=
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))
return
LOOKUPVALUE('dbma76 cm_Scheduled_Maintenance_Asset'[Operators],'dbma76 cm_Scheduled_Maintenance_Asset'[ID],Asset_ID)

[Jack11]

@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

[MarketingGerens]

This helped me, even with multiple filters.