Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Finding matching substring with DAX

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

nonoka_0-1651754837957.png

 

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

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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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:

nonoka_0-1652023970921.png

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?

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
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you @Ashish_Mathur 🙂

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DouweMeer
Post Prodigy
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:

nonoka_0-1651955357149.png

Is there a different solution?

 

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.