cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fjmocke
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
Zubair_Muhammad
Community Champion
Community Champion

Hi @fjmocke

 

Could you please Try this alternative

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

23 REPLIES 23
dianalucia_mtz
Frequent Visitor

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])
)

WhatsApp Image 2021-02-17 at 19.46.51.jpeg

 

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

WhatsApp Image 2021-02-17 at 19.47.05.jpeg

 

 

 

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

 

 

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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]))CALCULATECALCULATELOOKUPVALUE ErrorLOOKUPVALUE Error

Zubair_Muhammad
Community Champion
Community Champion

Hi @fjmocke

 

Could you please Try this alternative

 

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

Regards
Zubair

Please try my custom visuals

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 

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.

 

LiziM_0-1653315101283.png

 

Hello

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


Regards
Zubair

Please try my custom visuals

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)

@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

Jack11_0-1614087385531.png

 

This helped me, even with multiple filters.

Anonymous
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

 

 


Regards
Zubair

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.  

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors