Forum Discussion
Data categorization and labeling based on a criteria list
Ok I have solved this. Here's how:
I created a new column in my transaction table with the following formula:
MatchFound= FIRSTNONBLANK( FILTER( values('Keyword Table'[Narrative Contains]), search('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0) ) ,1)This formula returns the matching value found within the narrative string. I can then use this with a lookup formula, to return the corresponding category:
category = LOOKUPVALUE( 'Keyword Table'[Category],'Keyword Table'[Narrative Contains],'Transaction list'[MatchFound] )
And voila! My transaction list is now categorized by searching the a narrative field to see if it contains any item from a list of keywords paired with categories...
I found this solution for at least idenitfiying if there is a keyword match, but I still can't figure out how to return the corresponding category. This function can be thought of like a "containsx" formula.
In my transaction table I added a column with the following formula:
KeywordMatch = if(
SUMX('Keyword Table',
find(upper('Keyword Table'[Narrative Contains]),upper('Transaction list'[Narrative]),,0))
>0,"Match","No Match")Which gives me this result:
But I still can't figure out how to return the corresponding category...
Ok I have solved this. Here's how:
I created a new column in my transaction table with the following formula:
MatchFound=
FIRSTNONBLANK(
FILTER(
values('Keyword Table'[Narrative Contains]),
search('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0)
)
,1)This formula returns the matching value found within the narrative string. I can then use this with a lookup formula, to return the corresponding category:
category = LOOKUPVALUE( 'Keyword Table'[Category],'Keyword Table'[Narrative Contains],'Transaction list'[MatchFound] )
And voila! My transaction list is now categorized by searching the a narrative field to see if it contains any item from a list of keywords paired with categories...
Hi,
I'm using your solution on a similar DB.
My only issue is that I don't know how to customized the matching algortithm to include only exact matches. For example, one of my keyword is "pers" but this formula gives a match also when "personnel" is found. Do you know how I can request exact matches?
Thanks!
Thanks, it works for me. Perfect
