Forum Discussion
Search for specific text values from a dictionary column
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 ! 🙂
5 Replies
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.
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 ShengHi,
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.
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
