Forum Discussion
Lookupvalue value error
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 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])
Then 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]))
Look up errorCalculate
7 Replies
Anonymous ,
Try adding additional column in Table2:Column = CALCULATE( FIRSTNONBLANK(Table2[Mark], Table2[Mark]), ALLEXCEPT(Table2, Table2[Id] ) )
-- Mark is value that i wanted to return in Table1, but there are different marks for same Id.-- So i added new column which returns always same Mark for same Id (first occurence)
Now when i return to Table1 where i want to add lookupvalue, i will not search by Table2[Mark], but by my new column which has unique values per Id.
Lookup formula:Lookup column = LOOKUPVALUE(Table2[Column],Table2[Id],Table1[Order Number])I tried that and got too many arguments. So, here is what I am tryining to do, may there is a better way to do it.
In table FedEx Query I want to add a new column that will show a value. I need to use the Shipper Reference column to match the same value in column Pallet_ID in the table called DLX Report Query. That value I need to show in the new column in the table FedEx Query is called Dealer Code in the DLX Report Query. Sorry I am a little new at this, so it a bit confusing.
'FedEx Query'[Shipper Reference] Need too use this number
'DLX Report Query'[PALLET_ID] And Reference this number
'Report Query'[Dealer Code] Need to show this column that has duplicates
Anonymous ,
I checked your screenshots.
Second image means that there is no unique value for parameters that you use in lookupvalue function. It means that you should add some more parameters which will return unique value.
First image, the logic is to return max value per some criteria. Currently your return value is [Dealer Code] and you are searching max dealer code per dealer code [allexcept(dealer code)]. So it will not find unique values.
So here are steps:
In first image, first parameter is min value that you want to return per specific criteria. And criteria should be inside AllExcept function (AllExcept means group by: so you will get some value per criteria1, criteria2, criteria3..).
In second image make sure to have as many lookup parameter as needed to make sure you have unique value to return.
Anonymous
how do you define 'grab the first one'? by date?
i create a simple sample data for testing
try to create a column
Column = MAXX(FILTER(TableA,TableA[orderid]=TableB[orderid]&&'TableA'[date]=MINX(FILTER(TableA,TableA[orderid]=TableB[orderid]),'TableA'[date])),TableA[value])
