Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
bullius
Helper V
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 
ValueNew Column
ATRUE
BFALSE
CTRUE
DFALSE
E

TRUE

Table2
Value
A
C
E

 

Any Ideas?

 

Thanks!

 

2 ACCEPTED SOLUTIONS
KGrice
Memorable Member
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

 

See this post for more information about how each method works.

View solution in original post

jahida
Impactful Individual
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])

View solution in original post

8 REPLIES 8
KGrice
Memorable Member
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

 

See this post for more information about how each method works.

Anonymous
Not applicable

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

Thanks for the reply.

 

Table2 does not contain unique values...

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?

Yes! The second one does the job. Thanks!

jahida
Impactful Individual
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 ?

KGrice
Memorable Member
Memorable Member

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.