Lookup partial text from unrelated table

[Brandonkersgoed]

Hey, 

I'm new to DAX and am having some difficulty looking up partial text matches from an unrelated table. I'm hoping to run comparisons on company names, I essentially want to add a column to Table 1 that just has the company names, and in table 2 I have the names of all the companies we work with. Sample columns from tables as follows, any help would be greatly appreciated:

Table 1 Column:

Company Product

Generic Life Insurance Company Term 10

Another Generic Life Insurance Company Whole Life

Generic Life Insurance Company Term 20

3rd life insurance company term 20

term 20

Table 2 Column

Company Name

Generic Life Insurance Company

Another Generic Life Insurance Company

3rd life insurance company

[Brandonkersgoed · Accepted solution]

For anyone reading who needs an answer, this thread had what I was after:

https://community.powerbi.com/t5/Desktop/Partial-match-from-unrelated-tables/m-p/573100

[maryg]

Hi there - This works well, but i feel like it takes so long.  I am extracting text from an unrelated column and it takes forever to run.  I was wondering if this could be done as a calculated table?

[Brandonkersgoed]

Hi @tamerj1 Thanks for the help,

However the column came up blank (no errors at least!) 

I tried updating to the following as the product names and company names are on separate tables, I imagine I made a mistake somewhere:

Company = MAXX (

    FILTER (

        'Table 2',

        CONTAINSSTRING ( 'Table 2'[Company Name], 'Table 1'[product name] )

    ),

    'Table 2'[Company Name]

)

[tamerj1]

@Brandonkersgoed 

You are right. Totally my mistake 

Company = MAXX (
    FILTER (
        'Table 2',
        CONTAINSSTRING ( 'Table 1'[Company Name], 'Table 2'[product name] )
    ),
    'Table 2'[Company Name]
)

[tamerj1]

Hi @Brandonkersgoed 

please try

NewColumn =
MAXX (
    FILTER (
        'Table 2',
        CONTAINSSTRING ( 'Table 2'[Company Name], 'Table 2'[Product Name] )
    ),
    'Table 2'[Company Name]
)