Finding matching substring with DAX

[nonoka]

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...........

[Ashish_Mathur]

Hi,

This calculated column formula works

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

Hope this helps.

[nonoka]

Hi @Ashish_Mathur,

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?

[Ashish_Mathur · Accepted solution]

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.

[nonoka]

Thank you @Ashish_Mathur 🙂

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

[Ashish_Mathur]

You are welcome.

[DouweMeer]

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.

[nonoka]

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

Is there a different solution?

[DouweMeer]

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.