find keyword in text and add category based on found keyword

[EUR_MSC]

i tried LOOKUPVALUE - SEARCH, tried DAX and power query but no success.

searched the forum but cant find the awnser as well. so here it goes!

i got 2 tables

- CallCategory

- report

my question is as follow

if CallCategory[keyword] excists in report[text] than insert CallCategory[category] in report[category]

like a conditional column only the input of the values is a table

thanks in advance!

[v-zhangti · Accepted solution]

Hi, @EUR_MSC 

You can try the following methods.
Measure:

Measure = RIGHT(LEFT(SELECTEDVALUE(Report[text]),FIND("keyword",SELECTEDVALUE(Report[text]),1,BLANK())+7),8)

category 2 = CALCULATE(MAX(CallCategory[category]),FILTER(ALL(CallCategory),[keyword]=[Measure]))

Find function: Returns the starting position of one text string within another text string.

Then use Right and Left to extract the string, which can be compared with the keyword column of the CallCategory table.

FIND function (DAX) - DAX | Microsoft Learn

RIGHT function (DAX) - DAX | Microsoft Learn

LEFT function (DAX) - DAX | Microsoft Learn

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EUR_MSC]

almost there!

it works if your keywords are always the same number of characters.

i got keywords like

audio

headset

microfoon

controller

etc.

Measure = RIGHT(LEFT(SELECTEDVALUE(Report[text]),FIND("keyword",SELECTEDVALUE(Report[text]),1,BLANK())+7),8)

if i edit the 7 to 2 it shows the keyword "pc" if i change the 7 to 5 it shows the keywords "audio" & "video"

[MAwwad]

Hello,

Try this:

IF(CONTAINSSTRING(Keyword,text),category,BLANK())

Dont forget to do the necissary changes also

Dont forget to accept this as a solution if it helped you 🙂

[EUR_MSC]

looks like you cant use this over 2 tables? i can only select values from the table report or measures

[MAwwad]

If you can have a relationship between the two tables, then call the columns that you want to evaluate from the other tables using lookup value.

I think this can work only if there is a validated relationship between the two tables