Lookup by keyword in DAX | Contains Logic | Power BI and Power Pivot

Graciela Martínez
August 16, 2024

We want to analyze how invoicing works in our company, and we would like to find out which service category is the one we’re selling the most and which we could improve. So, we pull data related to all invoices we’ve sent to clients, and we find the following table:

As you notice, invoice line description is not consistent or standard across invoice details so if we want to use the column as is, it will be difficult to analyze data properly. We need to categorize each row into the 4 following groups:

Adjustments
After Hours Labor
Projects
Ongoing support

Going forward we can work in standardizing how invoices are issued and which text we indicate in the invoice line, but for historic data we need to apply some rules to categorize into the groups listed above.

Create a Category – Keyword table

First, let’s think about the rules we want to apply. For example, if line description contains “Project – “ then we want to categorize this as “Projects”, or if it has the keyword “Adjustment”, then this line should go to “Adjustment”, and so on. For this scenario we will focus on the following four groups and corresponding keywords

If different words map to a single category, just add a new row in the table for each of the different variations the group could have:

Add a calculated column in the invoice lines table

We have two tables:

Invoice Details
Keywords

We want to check the “invoice_description” column in each of the rows and see if any of the keywords listed in the Keywords table are contained in this description.

If there is a keyword that matches, we want to get which category the match is for.

Code used for this calculated column:

				
					Billing Category =   
//current line text, to lowercase 
VAR currentrowtext = 
    LOWER ( 'Invoice Details'[invoice_description] ) 
//search all keywords and find if any match 
VAR searchrow = 
    MINX ( 
        FILTER ( 
            Keywords, 
            SEARCH (Keywords[Keyword], currentrowtext, 1, 0 )  
            //search current row in the keywords table and filter if there is any match 
        ), 
        Keywords[Category] 
        //return the first found category  
    )  
//check if there is any match, if not, return "Other" 
RETURN 
    COALESCE ( searchrow, "Other" )

Use the new column in your visualizations

Now we’re more than ready to start analyzing our data with the new category table we added!