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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Hello all, I still have error in using Vlookup and FirstnonBlank. Below are the errrors. they are unique and no empty values.

 

Can anyone please explain me the error and help to solve. I need to lookup  a column from other table based on two columns values.

Krishna09_0-1708072271839.png

ERROR:-------

A single value for column 'payment_term' in table 'Modaly Spend Report FY23' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.



 

Krishna09_1-1708072351461.png

ERROR  2---------

Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

 

1

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

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

 

Amy

Hi evryone,

After many searches:

- i find a column has a type 'Standard', i transform this type to 'Numeric'

- the second thing, in the power Query, i find a value of 'Null' in somme coloumn numeric (from excel files), i replace by 0;

and i try many fomula after, and it's working finally, my problem was in this direction and not in the formula, it's work now with différent formula, it's give me the same result.

Thanks for your help (and sorry for my english :))

Regards

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.