topic Re: seaching for keywords in text for use as index seach function in DAX Commands and Tips

seaching for keywords in text for use as index seach function

Anonymous — Thu, 11 Jun 2020 06:50:25 GMT

I have a text string containing key words that I want to index so I can filter based on these words. The below measure, however, duplicates the Category if keys are included multiple times, for instance if both cod and herring is in the same text string it gives “FishFish” rather than Fish. I would also like to have a delimiter between multiple words such as “Animal, Insect”. Any tips?

Key's and their Category;

Category	key
Animal	dog
Animal	cat
Fish	cod
Fish	herring
Insect	wasp

Text with the added column and measure filter1 shown below.

Text	filter1
Dogs don't eat cod	AnimalFish
In cat	Animal
Fishing for Herring and cod	FishFish
No wasp stings dogs	AnimalInsect
Raining cats and dogs but not cod	AnimalAnimalFish
Wasping the herrings in the codcat	AnimalFishFishInsect

filter1 = var result= CONCATENATEX( 'Key', if( Search(FIRSTNONBLANK('Key'[key],1),Text1[Text],,999)<> 999, 'Key'[Category],"" ) ) Return if( result<>bLANK(), result, "-" )

Re: seaching for keywords in text for use as index seach function

lbendlin — Thu, 11 Jun 2020 12:05:33 GMT

The delimiter is the easy part

filter1 = var result= CONCATENATEX( 'Key', if( Search('Key'[key],Text1[Text],,999)<> 999, 'Key'[Category] & "," ) ) Return if( result<>bLANK(), left(result,len(result)-1), "-" )

But the repetitions are more tricky. One question - can you guarantee that the Key table is sorted alphabetically by Category?

Re: seaching for keywords in text for use as index seach function

Anonymous — Thu, 11 Jun 2020 13:53:18 GMT

Hi Anonymous ,

Text Data is the table which contains your text.

Words table is which contains your Category and Key

You will need to create an id for your category in Power Query.

1. Group your Word Table by Category.

Create an Index Column from Add Columns. Start with Index 1.

Expand the key Column only

Create a Calculated Column

Found =

var a = filter(

all('Words'[key]),

search('Words'[key],TextData[Text ],1,0)<>0

)

RETURN

calculate(

CONCATENATEX(values('Words'[Category]),Words[Category],","),a)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: seaching for keywords in text for use as index seach function

lbendlin — Thu, 11 Jun 2020 14:22:45 GMT

Anonymous great approach. But it's cheating 🙂 Any way to do that indexing in DAX?

Re: seaching for keywords in text for use as index seach function

Anonymous — Thu, 11 Jun 2020 14:58:45 GMT

Thanks alot, this works perfectly!

Re: seaching for keywords in text for use as index seach function

Anonymous — Thu, 11 Jun 2020 16:33:23 GMT

Hi lbendlin ,

DAX to create an index column

Id_Dax =

RANKX(ALL(Words[Category]),CONCATENATE(Words[Category],""))

Regards,

Harsh Nathani