Compare text string between two tables (Not exact)

[TIGER8855]

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.

[Anonymous · Accepted solution]

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

[TIGER8855]

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

[Anonymous]

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

[TIGER8855]

@Anonymous thank you! Worked perfectly.

[Adamboer]

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.

[TIGER8855]

Thanks for the response @Adamboer . I tried the suggested formula however I get the error "Too few arguments were passed to the CONTAINS function. The minimum argument count for the function is 3.