cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Finding matching substring with DAX

I have two tables "Sentences" and "Words".

My goal is to add a calculated column/measure to the "Sentences" table, which contains the words that also occur in the Words table.

The "Sentences" table's both "text" and "id" collumns are calculated measures. The "Words" table is just simply imported.

The tables in the example are only examples, there might be more and different sentences, words in each table.

Right now, the two tables are not joined.

I would like to write a DAX function that would create the third collumn (highlighted with yellow).

Does anyone know a solution for this problem? I was thinking about taking the first sentence --> iterating through the Words table --> finding / not finding a match --> moving onto the next sentence --> iterating...........

1 ACCEPTED SOLUTION
Super User

Hi,

From the 1 column Words table, build a 3 column table with the original, English and Spanish columns.  Modify my calculated column formula to:

``Words found = CONCATENATEX(FILTER(VALUES(words[Words]),CONTAINSSTRING(sentences[text],words[English])),words[Words],",")``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Super User

Hi,

This calculated column formula works

``Words found = CONCATENATEX(FILTER(VALUES(words[Words]),CONTAINSSTRING(sentences[text],words[Words])),words[Words],",")``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Your solution works perfectly, however I forgot one important detail in my question. I need to find substrings from the "words" table as well. Example:

The matching substrings are followed by " - " and then another set of substrings are also present. (It is an English - Spanish dictionary).

Is there a solution for this problem?

Super User

Hi,

From the 1 column Words table, build a 3 column table with the original, English and Spanish columns.  Modify my calculated column formula to:

``Words found = CONCATENATEX(FILTER(VALUES(words[Words]),CONTAINSSTRING(sentences[text],words[English])),words[Words],",")``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Thank you @Ashish_Mathur 🙂

After the VALUES function I had to pass the "Words" table and not the "Words[WORDS]" collumn.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Prodigy

Probably a combination of containsstring, concatenatex, and, personally, selectcolumns.

``````selectcolumns(
'table sentences'
, "Id" , 'table sentences'[Id]
, "Text" , 'table sentences'[text]
, "Words"
, concatenatex (
filter ( 'table words'
, containsstring ( 'table sentences'[text] , 'table words'[words] )
)
,  'table words'[words]
, ", "
)
)``````

Haven't tried it, I may have missed misspelled something.

Anonymous
Not applicable

I have tried it, but unfortuanatelly received this error message:

Is there a different solution?

Post Prodigy

Looking at the accepted solution, your proposed of two tables in to one was meant as an addition on the first. I thought you wanted a third table.

If you would put what I wrote into a new custom table, it should work.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors