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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you Ashish_Mathur 🙂

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      You are welcome.