cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
fjmocke
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)

 

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

33 REPLIES 33
JenWilson
Advocate I
Advocate I

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

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

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

 

Amy

   

It works.

saved lot of time for me. Thanks 🙂

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

Anonymous
Not applicable

Hello, I am trying this query but not helping 

Swatipandey_0-1660775969560.png

Swatipandey_1-1660776059531.pngSwatipandey_2-1660776073526.png

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

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

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

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

 

Use double ampersand instead.

For condition AND  = &&

For condition OR = ||

 

Or 

 

You can use method AND(condition1, condition2)

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.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors