Forum Discussion
Compare text string between two tables (Not exact)
I have columns in seperate tables and I need to identify if there is match between the columns. The text in table 1 will be exact where as the text in table 2 may contain the text from table 1 amongst other text. I cannot create a relationship between these table columns as some of the values in Table 1 are duplicated.
If possible, I want a new column in table 1 which will have a yes or no for each row to indicate if there is a match in Table 2. Using DAX would be the preference. Below is a sample of the data
| Table 1 | EXPECTED RESULT |
| AAA-1111 | Yes |
| BBB-2222 | No |
| CCC-3333 | Yes |
| AAA-1111 | Yes |
| DDD-4444 | No |
| Table 2 |
| AAA-1111 This is a sample |
| Sample Data. EEE-5555. Testing |
| Test of data CCC-3333. |
| HHH-8888. |
Hi TIGER8855 ,
You can create a calculated column as below in 'Table 1', please find the details in the attachment.
Column = VAR _t2col = CALCULATE ( MAX ( 'Table 2'[Column1] ), FILTER ( 'Table 2', IFERROR ( SEARCH ( 'Table 1'[Column1], 'Table 2'[Column1], 1, 0 ), 0 ) > 0 ) ) RETURN IF ( ISBLANK ( _t2col ), "No", "Yes" )Best Regards
6 Replies
Hi TIGER8855 ,
You can create a calculated column as below in 'Table 1', please find the details in the attachment.
Column = VAR _t2col = CALCULATE ( MAX ( 'Table 2'[Column1] ), FILTER ( 'Table 2', IFERROR ( SEARCH ( 'Table 1'[Column1], 'Table 2'[Column1], 1, 0 ), 0 ) > 0 ) ) RETURN IF ( ISBLANK ( _t2col ), "No", "Yes" )Best Regards
Thank you Anonymous ! That worked. The solution provided was well written.
I have one more request if you could help which is related. I also need an additional column that uses the same concept against the same source data with the only difference that it also needs to filter against an additional column in Table2 that has either TRUE or FALSE in it. So if Column1 from Table1 has a match against the text string in Table2 Column1 AND Column 2 is TRUE then it would return a Yes in the new Column in Table1. If not it would return a No. Below is an example of the expected result for new Column:
Table1
Column1 Column AAA-1111 No BBB-2222 No CCC-3333 Yes AAA-1111 No DDD-4444 No Table 2
Column1 Column 2 AAA-1111 This is a sample FALSE Sample Data. EEE-5555. Testing TRUE Test of data CCC-3333. TRUE HHH-8888. FALSE Hi TIGER8855 ,
You can create a calculated column as below similar to the previous one to get it:
Column = VAR _t2col = CALCULATE ( MAX ( 'Table 2'[Column1] ), FILTER ( 'Table 2', IFERROR ( SEARCH ( 'Table 1'[Column1], 'Table 2'[Column1], 1, 0 ), 0 ) > 0 && 'Table 2'[Column 2] = "TRUE" ) ) RETURN IF ( ISBLANK ( _t2col ), "No", "Yes" )Best Regards
Yes, it is possible to use DAX to create a new column in Table 1 that indicates whether there is a match in Table 2. One approach could be to use the CONTAINS function to check if the text in Table 2 contains the text in Table 1.
Here is an example DAX formula that you can use to create a new column in Table 1:
=IF(COUNTROWS(FILTER(Table2,CONTAINS(Table2[Text],Table1[Column])))>0,"Yes","No")
This formula uses the FILTER function to identify rows in Table 2 where the text in the [Text] column contains the text in the [Column] column of Table 1. The CONTAINS function performs the actual text comparison. If the FILTER function returns any rows, then the COUNTROWS function will return a value greater than zero, indicating that there is a match. The IF function then returns "Yes" if there is a match, and "No" otherwise.
Note that this formula assumes that the name of the [Text] column in Table 2 is "Text". You may need to adjust the formula accordingly if the actual name of the column is different.
Also, keep in mind that this approach may not be very efficient for large tables, as it requires scanning the entire [Text] column of Table 2 for each row in Table 1. If performance is a concern, you may want to consider other approaches, such as using Power Query to merge the two tables based on a partial match of the [Text] and [Column] columns.
