Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

New Column Lookupvalue with a filter on another Tab

Hi together,  I have to check if  in the table "data_Current" the email is matching with an email in the table "users" and it should only match when the User in the table "users" does have an model ...
Need Help
vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
4 years ago

Hi, Anonymous ,
I tested your sample data and you got an error in the Devices table as IMEI should be unique, however, with modifying one row, here is my working solution:

Check = 
var Model_Check = LOOKUPVALUE(Devices[Model],Devices[IMEI],Current_Data[IMEI])
var MAIL_Check = LOOKUPVALUE(Users[EMAIL],Users[EMAIL],Current_Data[Email])

RETURN IF(Model_Check="Android" || Model_Check="IOS" && NOT(ISBLANK(MAIL_Check)), "Yes", "No")

 

Please note, you only need the "Check" column, I added the two more just for you to clarify what'S going on.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi, 
as you can see in the Solution. User test.1 & test.3 & test.100 should be Yes.
I have updated the description with one picture, maybe this help

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    4 years ago

    Hi,
    Anonymous 

    I found a few inconveniences in your sample data:
    If you wanna relate the tables using "user ID", [email protected] cannot Return True, also you have duplicity in Devices, meaning the Lookup can't tell which row should be assigned to. Also, you have a typo in [email protected], his device is "Andorid", not "Android", thus it can't return TRUE. 

    If I delete duplicity and ignore Test10, here is my solution:

    Check = 
var Model_Check = LOOKUPVALUE(Devices[Model],Devices[User-ID],Current_Data[UserID])
var UserID = RELATED(Users[USER-ID])

RETURN IF(Model_Check="Android" || Model_Check="IOS" && NOT(ISBLANK(UserID)), "Yes", "No")

     




    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      is ther another possible solution ?

      • vojtechsima's avatar
        vojtechsima
        Icon for Super User rankSuper User
        4 years ago

        Hi, Anonymous 

        if you don't care about the information that doesn't meet your criteria, you can filter the wanted criteria in Power Query and load only the correct data.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yeah, that is the issue, but i cannot not delete the duplicated data, however, is there another possible solution to show the expected outcoming ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yeah, that is the issue, but i cannot not delete the duplicated data, however, is there another possible solution to show the expected outcoming ?