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
Icon for Resident Rockstar rankResident 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

 

 

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

  • nandic's avatar
    nandic
    Icon for Resident Rockstar rankResident 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? 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous,

        Can you please share some dummy data to test? It should help to test and trouble your code.

        How to Get Your Question Answered Quickly 

        Notice: remove sensitive data before share.
        Regards,
        Xiaoxin Sheng