Search for specific text values from a dictionary column

[enzos]

Hi, I'm trying to recreate an excel report to PBI. What i am having troubles recreating is the dictionary table which allows us to search for an specific keyphrase in Table A and categorize them into groups.

Example:

Table A

Incident Number	Submit Date	Description
Incident001	4/1/2020	Ice cream machine broken again
Incident002	4/2/2020	Candy Machine is low on chocolate bars
Incident003	4/3/2020	Washing machine on Floor b not working
Incident004	4/4/2020	Low on paper floor 1

Dictionary Table

KeyPhrase 1	KeyPhrase 2	Display as Category
Ice cream	Machine	Repairs - Maintanence
Candy	Machine	Repairs - Maintanence
Washing	Machine	Repairs - Maintanence
Paper		Restock

Then we run some macros that would search the description column in Table A to find any matches in the dictionary table from KeyPhrase1 and KeyPhrase2, if there is a match then the category would be added as an extra column.

Table A

Incident Number	Submit Date	Description	Category
Incident001	4/1/2020	Ice cream machine broken again	Repairs - Maintanence
Incident002	4/2/2020	Candy Machine is low on chocolate bars	Repairs - Maintanence
Incident003	4/3/2020	Washing machine on Floor b not working	Repairs - Maintanence
Incident004	4/4/2020	Low on paper floor 1	Restock

I was able to find a similar problem here but the user wanted the fix to be done in M, however i want it to be done using DAX if possible as we would be manually adding more information to the dictionary table.

Thank you in advance ! 🙂 

[v-shex-msft]

Hi @enzos,

I'd like to suggest you take a look at the following blog about string Comparison in power bi DAX expressions:

From SQL to DAX: String Comparison 

Regards,
Xiaoxin Sheng

[enzos]

Hi,

I read through the article but sadly i coudlnt find something that could help, what im looking for is a way to search a column against another column, and if it finds a match to then give me the corresponding category.

I am looking for a way to find a keyphrase from table dictionary in column description from Table A, and if there is a match with one of the keyphrases to match it and pull the corresponding category.

I could create multiple columns that each search for a keyphrase but as we add more and more keyphrases that would be inefficient.

[v-shex-msft]

HI @enzos,

According to your description, I think you may need 'machine learning' or AI insight to recognize and analyze your table records instead of manually checking and replace text with DAX formula. You can take a look at the following document to know more about these:

Use AI Insights in Power BI Desktop 

Regards,

Xiaoxin Sheng

[dkpmfa]

Anyway, where I'm going with that is, if you have a limited number of search words, you can add a column to your table for each search word like this:

SearchField1 = SEARCH("<keyword to search for>",'<your table>'[<column to search>],1,0)

Then you will either have a 0 in the column if the keyword isn't in the field being searched or a non-0 if it is. 

[dkpmfa]

Do you have a finite number of key words to look for?