Dax - Keyword/Category replacement.

Hi CrownWorker -

I believe you are getting the error on the second set because you need to ensure that your keyword list has a unique set of values. i.e. you can't have the same keyword listed in your keyword table more than once.

The formula I gave you above returns the category in alphabetical order. So if there are multiple matches, the item that comes first alphabetically is what appears in the category formula.

Sounds like you want to override this alphabetic sorting and return the category by your own ranking - where you define the order of categories to return in the case of multiple matches. Here's how:

Step 1: Add a column to your keyword table and rank your keyword table like so:

Now change your matchfound formula to something resembling the following:

rankedCategory = 

LOOKUPVALUE(
    'Keyword Table'[Narrative Contains],'Keyword Table'[Rank],
     CALCULATE(
        FIRSTNONBLANK('Keyword Table'[Rank],1),
        filter(
           VALUES('Keyword Table'[Narrative Contains]),
           SEARCH('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0)<>0
        )
     )
)

Which should now create the following:

This should allow you to assign a ranking in order to dictate which categories should be returned in the case of multiple matches.