cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
nonoka
Frequent Visitor

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/

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/

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.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors