cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V

## If column contains values from column in another table...

Hello

I would like a column that shows whether or not a column in Table2 contains values that are in Table1.

 Table1 Value New Column A TRUE B FALSE C TRUE D FALSE E TRUE
 Table2 Value A C E

Any Ideas?

Thanks!

2 ACCEPTED SOLUTIONS
Memorable Member

Hi @bullius. If table 2 contains only unique values, you could relate the two tables on the Value column, and then use this formula for your New Column:

`New Column = NOT(ISBLANK(RELATED(Table2[Value])))`

You can also use the formula below, which will work with or without the relationship:

`New Column = CALCULATE(COUNTROWS(Table2), FILTER(Table2, Table2[Value]=Table1[Value])) > 0`

Impactful Individual

I know KGrice's formula worked but it does seem a tad clunky... you could consider this as a slightly cleaner alternative (since the CONTAINS function exists exactly for this purpose):

Column = CONTAINS(Table2, Table2[Value], Table1[Value])

8 REPLIES 8
Memorable Member

Hi @bullius. If table 2 contains only unique values, you could relate the two tables on the Value column, and then use this formula for your New Column:

`New Column = NOT(ISBLANK(RELATED(Table2[Value])))`

You can also use the formula below, which will work with or without the relationship:

`New Column = CALCULATE(COUNTROWS(Table2), FILTER(Table2, Table2[Value]=Table1[Value])) > 0`

Anonymous
Not applicable

Although I have a relationship the second option is not working.

Helper V

Table2 does not contain unique values...

Memorable Member

Hi @bullius. The unique values piece only matters for creating a relationship. The second option I listed does not require unique values in either table. Were you able to get that one working?

Helper V

Yes! The second one does the job. Thanks!

Impactful Individual

I know KGrice's formula worked but it does seem a tad clunky... you could consider this as a slightly cleaner alternative (since the CONTAINS function exists exactly for this purpose):

Column = CONTAINS(Table2, Table2[Value], Table1[Value])

Anonymous
Not applicable

but with this method, there has to be a relationship established between tables, right ?

Memorable Member

Thanks, @jahida. I hadn't come across the CONTAINS function yet. I'll start using that instead.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors