Dax - Keyword/Category replacement.

[CrownWorker]

Ok here is my problem,  I have been using Access with Excel to this point to build my reports and am transitioning to PowerBI and very little understanding of Measures and DAX. 

the SQL I am using is as follows:

UPDATE MainTable, CategoryTable

SET MainCat = Category
WHERE TRBL_DESC Like "*" +CategoryTable.Keyword + "*";

MainTable - the table being used in PowerBI where I have a TRBL_DESC "trouble description" column and have created a blank column "MainCat" to populate from the SQL above.

CategoryTable - has 2 columns "Keyword" and "Category"

What the SQL does is pull in both tables, then look to see if TRBL_DESC field in the MainTable contains any of the keywords within its string from the CategoryTable Keyword fields then replaces the MainCat field in the MainTable with the Category field in the CategoryTable. 

I would like to do this all in PowerBi, and I dont want to have a long string because sometimes my keywords are in the 1000's and I need to be able to easily reorder them.

In short I need the Dax Equivalent for the SQL.

Thank you.

[v-jiascu-msft]

Hi @CrownWorker,

Import these two tables first. Then add a calculated column with this formula.

MainCat =
LOOKUPVALUE (
    CategoryTable[Category],
    CategoryTable[Keyword], MainTable[Trbl_Desc]
)

Note: the records (rows) in "CategoryTable" should be unique.

Best Regards!

Dale

[CrownWorker]

Thank you for the attempt, the only problem with your solution is that it needs an exact match and I am looking for partial/similar match

[robbieinoz]

Hi @CrownWorker

I had a similar problem and just figured it out. Here's what I did:

I had a transaction table with various narratives. I wanted to search each description narrative to see if it contains a keyword from a keyword list If it did, return the corresponding category paired with the keyword. So like this:

Transaction list:

Keyword List:

The first thing I did was set up a column to determine if there's a match, and if so - what keyword identified the match. I created a calculated column with this formula:

MatchFound = 
FIRSTNONBLANK(
FILTER(
values('Keyword Table'[Narrative Contains]),
search('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0))
,1)

Which adds this column to the table - that identifies the keyword that was found in the narrative string. 

I then used a lookupvalue formula - to return the corresponding category like so:

category = 
LOOKUPVALUE(
'Keyword Table'[Category],'Keyword Table'[Narrative Contains],'Transaction list'[MatchFound]
)

Which adds this category column. Voila!

Hope that helps!

[CrownWorker]

The first part worked, I would like to understand how it has ordered my list of keywords so that I can order them properly to capture the information in the best possible way to represent the service.

The second part isnt working properly though giving the error indicated in the graphic below

[robbieinoz · Accepted solution]

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.

[CrownWorker]

Hey Robbieinoz,

You are amazing. That solved every issue I had. I wish there was a way I could show my appreciation better then a few lines of text on a screen.  I'll try to send some goodwill and karma your way.

Thanx again.

[robbieinoz]

No worries happy to help 🙂