Look up value from an unrelated table using text comparison

[Anonymous]

Hi all,

In a calculated column, I'm trying to insert a looked-up value from an unrelated table using a "contains" text comparison.

I understand how to use FIND to test that one text string contains a second. I can't figure out how to obtain the row and subsequent value from the second table using that logical test. I tried using FILTER, but I'm stuck getting at the actual results of that filter.

The first table has a Description column containing a long text value, e.g.  "Whole Foods DBCA 185626".

The second table has two columns, a Short Description text field ("Whole Foods") and a Sub-Category field ("Grocery") which is the value I want inserted into the first table.

So, essentially: Find the row in Table 2 where Table1[Description] contains Table2[Short Description] and then return the Sub-Category value from that row.

Thanks in advance for your help!

[amitchandak · Accepted solution]

Try a new column like

New column in Table 1 = minx(filter(table2,search(Table2[Short Description],Table1[Description],1,0)>0),Table2[Sub-Category])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Thank you, that did it!

I was on the right track with the FILTER function, but I didn't know how to derive a scalar value from the resulting table. MINX seems to work fine, though I wonder if there are other ways.

Thank you!

[MartynRamsden]

Hi @Anonymous 

It's likely the LOOKUPVALUE function can help dax.guide/lookupvalue/ 

Best regards,
Martyn

[Anonymous]

Try the LOOKUPVALUE function!

[Anonymous]

Thanks for the reply. I looked at LOOKUPVALUE previously, but as far as I can tell it does a straight value comparison (value1==value2) to find a match, and doesn't allow for the "text contains" comparison I need. Please correct me if this is wrong.

[MartynRamsden]

@Anonymous 

Correct - LOOKUPVALUE will only return results if there's an exact match.

Maybe I misread your original post but it sounded like you knew what you were seaching for?!

Are you able to share some sample data so we can better understand the issue your facing?

Best regards,
Martyn