Return records that match a list of specific string keywords

[maxxmilo]

Hi all, I'm new to Power BI and I've run into an issue. I want to calcuate a total based on 2 filters:

1) where Type is "Project"

OR

2) where Product Name matches at least one key word in a specified list

I'm especially having trouble calculating from the list of specific words - the record just has to contain that word, it doesn't have to be exact. I'm not sure what the right function to use would be - Find, Search, Contains, something else? Is there something that would allow me to say something like "return the record if the Product Name contains any of the following words ("Visit", "Fee") ? I might need to add more words to that list, so it would be great if there would be a way to just provide a list of possible words to search for.

Here's my code so far - I'd really appreciate any help with what I'm doing wrong. The "OR" is also tripping me up.

Total = SUM ( data[price],

FILTER ( data, data[Type] = "Project" ||

FIND ( "Visit", data[ProductName] ) ||

FIND ( "Fee", data[ProductName] )

) )

[Chihiro · Accepted solution]

You should use CALCULATE(SUM(),FILTER()) construct.

You should also specify Not Found Value argument for FIND.

Something like...

Total =
CALCULATE (
    SUM ( data[price] ),
    FILTER (
        data,
        data[Type] = "Project"
            || FIND ( "Visit", data[ProductName],, 0 ) <> 0
            || FIND ( "Fee", data[ProductName],, 0 ) <> 0
    )
)

[maxxmilo]

This worked! Thank you!! So if I'm understanding correctly, the NOT FOUND tells Power BI what to do in case it doesn't find the values, correct?

[Chihiro]

It should be value. Basically tells function what value to return as result if text isn't found. Most often set to 0, -1, or BLANK() depending on your need.

Oh, and if your list is constantly changing. I'd recommend storing list of texts in separate table.

Then you can peform calculation in Query Editor to check if [ProductName] contain any of the word in list.

You can find post I made about the subject in link below (post #11). It's written for Excel PowerQuery, but same applies to PowerBI.

https://chandoo.org/forum/threads/useful-powerquery-tricks-chihiros-notes.35658/

Then, you'd simply use that flag column in your filter condition.

[maxxmilo]

This is incredibly helpful, thank you!