Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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)
Solved! Go to Solution.
Hi @fjmocke
Could you please Try this alternative
Operator= CALCULATE ( FIRSTNONBLANK ( Shifts[TruckOperatorName], 1 ), FILTER ( ALL ( Shifts ), Shifts[UniqueID] = FuelData[UniqueShiftID] ) )
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.
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?
I'm having the same issue. Did you figure it out?
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]))
Hi @fjmocke
Could you please Try this alternative
Operator= CALCULATE ( FIRSTNONBLANK ( Shifts[TruckOperatorName], 1 ), FILTER ( ALL ( Shifts ), Shifts[UniqueID] = FuelData[UniqueShiftID] ) )
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.
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.
ERROR 2---------
Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.
1
thank you!!!!!!!!!!!!!!!!!
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!
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.
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
64 | |
55 |