Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    This calculated column formula works

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

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Ashish_Mathur
        Super User
        4 years ago

        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.

  • DouweMeer's avatar
    DouweMeer
    Impactful Individual
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

      Is there a different solution?

       

      • DouweMeer's avatar
        DouweMeer
        Impactful Individual
        4 years ago

        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.