Matching partial text from TableA to TableB

[lichan]

I'm trying to match an Address column in TableA from a column from TableB name Sites.

I tried adding a new column in TableA to give me True/False results based on the search but I'm unable to input TableB without an error? 

This is the formula I used:

Column = IF((SEARCH('TableB'[Sites], [Address]), [True, False]))

TableA:

First Name	Last Name	Address	New Column I want to create
John	Doe	123 Main St Apt 203	True
Johnthan	Dow	123 Main Street, Apartment 419	True
Janice	Dow	999 Market Street, suite 103	False
Jane	Doe	123 Main Street aprt 301	True

TableB:

Sites

123 Main

222 2nd

333 3rd

 I've also tried converting it into the list using the same formula but it still wouldn't work. Open to any suggestion. 

[v-shex-msft · Accepted solution]

Hi @lichan,

I think both search and find function should suitable for your scenario, you can also take a look at the following blog about a similar usage: (notice: find functions is case sensitive)

DAX – “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2 

Regards,

Xiaoxin Sheng

[amitchandak]

@lichan , Try a new column like in table A

if(isblank(countx(filter(tableB, search(Tableb[Sites], tableA[Address],,0)>0),Tableb[Sites])),true(), false())

Refer copy value from one table to another: https://www.youtube.com/watch?v=czNHt7UXIe8

[StefanoGrimaldi]

ok, for table A go over to power query, copy the column to a new column, separate it by dilimiter being the delimiter a blank space, you would get a column with the adress initial numbers ids lets say ( delete the others resulting columns), do the same for table B, them apply changes of the query and make a relationship between those 2 columns over in your data model if you dont need the relationship but only the column you can use a dax new column 

new columns =if( (lookupvalue ( table b[sites column], table B[new query column with sites numbers] , table B[new query column with sites numbers]))<>blank(),TRUE(),FALSE() )

if this solved your question please mark as solution, if you liked the solution give some kudos 🙂