Forum Discussion
Matching partial text from TableA to TableB
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:
| 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.
Hi Anonymous,
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
3 Replies
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 🙂
Anonymous , 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
Hi Anonymous,
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
