cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RuneStrand
Frequent Visitor

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)

 

Screen Shot 2020-01-22 at 14.20.53.png

 

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

 

Best regards

Rune

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Joren_venema Resolver V
Resolver V

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

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. 

 

View solution in original post

3 REPLIES 3
Joren_venema Resolver V
Resolver V

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

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. 

 

View solution in original post

Microsoft v-lionel-msft
Microsoft

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

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.

RuneStrand
Frequent Visitor

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

@Joren_venema 

 

Thanks! That did do the trick!

 

All the best 🙂 

 

/Rune 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors