Forum Discussion

o59393's avatar
o59393
Post Prodigy
4 years ago
Solved

Lookupvalue not working between 2 tables

Hi all

 

I am doing a lookup calcualted column, but doesn't work

 

Basically I have a table called 'Master Plant & Product' that has to xlooup its values on another table called 'SKU by line - Official'.

 

The IF statement I use is very simple:

 

IF value in column Merged Plant & Product of 'Master Plant & Product' table is found in column Merged Plant & Product of 'SKU by line - Official' table, then return an "Exists" else "Doesn't exist".

 

The example above is explained in this excel: https://1drv.ms/x/s!ApgeWwGTKtFdh2c9PC00KsBrHMrF?e=AMkZgI

 

I am trying to replicate that with a lookupvalue but gives me an error:

 

 

 

 

The relationship is a 1:M

 

 

 

Can you please advise how to make it work?

 

Thanks.

Need Help
Show and Tell
  • KNP's avatar
    KNP
    4 years ago

    Try this instead...

    IF('Master Plant & Product'[Merged Plant & Product] IN DISTINCT('SKU by line - Official'[Merged Plant & Product]), 1,0)

     

    I may have typo'd your column names so double check those.

11 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    As far as the report is concerned, what is the purpose of the 'Product Exists' column?

    Would you be happy for a Power Query solution or does it need to be DAX?

  • o59393's avatar
    o59393
    Post Prodigy
    4 years ago

    Hi KNP 

     

    The purpose is to have an alert system when user misses adding the correct data. Long story short I summarized it in that excel to not include all the model and sensible data.

     

    Can we have it in dax calculated column? 

    Thanks !!