Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maxxmilo
Helper I
Helper I

Return records that match a list of specific string keywords

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] )

) )

1 ACCEPTED SOLUTION
Chihiro
Solution Sage
Solution Sage

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
    )
)

View solution in original post

4 REPLIES 4
Chihiro
Solution Sage
Solution Sage

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
    )
)

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?

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.

This is incredibly helpful, thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.