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

[RuneStrand]

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

[v-lionel-msft]

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.

[Anonymous · Accepted solution]

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]

@Anonymous 

Thanks! That did do the trick!

All the best 🙂 

/Rune