Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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...
Need Help
nandic's avatar
nandic
Resident Rockstar
6 years ago

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



  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

     

     

    So I tried to do that and it didn't change any thing.

    • nandic's avatar
      nandic
      Resident Rockstar
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        You kinda lost me on that one. How do I need to write that to get it to work? I just want to get the dealer code to show up in the other table?