Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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,
"-"
)
Solved! Go to Solution.
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
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Thanks alot, this works perfectly!
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
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@harshnathani great approach. But it's cheating 🙂 Any way to do that indexing in DAX?
Hi @lbendlin ,
DAX to create an index column
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?
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |