topic Re: Lookup partial text from unrelated table in DAX Commands and Tips

Lookup partial text from unrelated table

Brandonkersgoed — Fri, 26 Aug 2022 21:10:48 GMT

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

Re: Lookup partial text from unrelated table

tamerj1 — Sat, 27 Aug 2022 15:07:11 GMT

Hi Brandonkersgoed

please try

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

Re: Lookup partial text from unrelated table

Brandonkersgoed — Mon, 29 Aug 2022 13:10:38 GMT

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]

)

Re: Lookup partial text from unrelated table

tamerj1 — Mon, 29 Aug 2022 13:13:31 GMT

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] )

Re: Lookup partial text from unrelated table

Brandonkersgoed — Mon, 29 Aug 2022 19:07:43 GMT

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

Re: Lookup partial text from unrelated table

maryg — Tue, 13 Aug 2024 17:05:04 GMT

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?