Forum Discussion

RuneStrand's avatar
RuneStrand
Frequent Visitor
6 years ago
Solved

Finding if values from multiple columns in table1 exists in one column of table2

Hi 

 

Fairly new to PowerBI, and with a relatively complicated data-set, I have been an active reader of posts in the community. I have however not been able to find a solution to a challenge:

 

Needed output: column with True/False or Yes/No values.

 

Input: 2 tables, let's call them Table1 and Table2.

 

In Table1, I have several columns with unique ID codes, containing both letters and numbers. Some rows will have values, and some not, some ID codes will have multiple occurrances, and some not.

In Table2, I have one column with the ID codes, where one ID code only occurs once.

 

I want to make a new Column in Table1, that has True/False or Yes/No, IF the ID code from any of the columns in Table1 can be found in Table2.

 

Could look something like this: (simply put in excell)

 

 

I hope it makes sense! and that there is a solution.

 

Best regards

Rune

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hello RuneStrand 

    To do this you can use lookupvalue to return true or false for each column in the table. When any of the 4 columns return a non blank the Output will be true else it will be false.

     

    it might not be the prettiest  piece of code, but it will do the trick.

     

    Here is the code:

     

    Calculation =
IF (
    NOT (
        ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code1] ) )
    )
        || NOT (
            ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code2] ) )
        )
        || NOT (
            ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code3] ) )
        )
        || NOT (
            ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code4] ) )
        );
    TRUE ();
    FALSE ()
)

     

    Kind regards
    Joren Venema

    Data & Analytics Consultant
    If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello RuneStrand 

    To do this you can use lookupvalue to return true or false for each column in the table. When any of the 4 columns return a non blank the Output will be true else it will be false.

     

    it might not be the prettiest  piece of code, but it will do the trick.

     

    Here is the code:

     

    Calculation =
IF (
    NOT (
        ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code1] ) )
    )
        || NOT (
            ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code2] ) )
        )
        || NOT (
            ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code3] ) )
        )
        || NOT (
            ISBLANK ( LOOKUPVALUE ( Table2[ID_Code]; Table2[ID_Code]; Table1[ID_code4] ) )
        );
    TRUE ();
    FALSE ()
)

     

    Kind regards
    Joren Venema

    Data & Analytics Consultant
    If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

     

    • RuneStrand's avatar
      RuneStrand
      Frequent Visitor
      6 years ago

      Anonymous 

       

      Thanks! That did do the trick!

       

      All the best 🙂 

       

      /Rune 

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi RuneStrand ，

    Maybe you can try this measure:

    Measure 4 = 
VAR x = COMBINEVALUES(",", MAX([ID_code1]), MAX([ID_code2]), MAX([ID_code3]), MAX([ID_code4]))
VAR y = FIRSTNONBLANK(Sheet11[ID_code], 1)
VAR z = 
FIND(
    y, x, 1, BLANK()
)
RETURN
IF(
    Z <> BLANK(),
    TRUE(), FALSE()
)

     

    Best Regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.