Column Text Analysis via Keyword Search

[annlois]

I'm currently trying to make a ticket pattern analysis from 3 different columns.

What I'm trying to do:

1- Search keywords of "Login issues" / "access request" etc etc on 3 different columns

2- Fill in a new custom column for the ticket category it will fall under

Example: If a keyword for "login" is found -> Custom column will be "Login Issue/Request"

The only solution i found was to make a conditional column for this. But unfortunately since there are a lot of keywords, I have a lot of else-if conditions to make. This is what my DAX formula looks like:

Is there a more dynamic way to do it in DAX? Or I can just stick to how I'm currently doing it?

Thanks for your help in advance! 🙂

[Anonymous · Accepted solution]

HI @annlois,

You can create a new table with keywords and correspond status.(e.g. Keyword, Status) Then you can use Dax expression add the calculated column in your table to look up the custom table records with current table fields values to output the result status.

If you are confused about coding formula, please share dummy data dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[annlois]

Hi Xiaoxin,

Thank you!!! I was able to create a new column with these formula:

Column1= MAXX(filter(ALL(TableA), CONTAINSSTRING([TableB.Resolution], TableA[Column1])),TableA[Column2])

Now I need to fill in my fields with not only the comparison from TableB.Resolution, but also with other tableB columns like TableB.Description and TableB.DetailedDescription. Maybe you have a hint how I can do it? 🙂