cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SamBerger Regular Visitor
Regular Visitor

Check if anything from a list appears in a text string

Hi all,

 

I've been working with a measure which checks if a selection in a filter appears in another table and filters accordingly. It looks like this:

 

VizData_Matched = VAR searchvalue=SEARCH(SELECTEDVALUE('Campaign Filter'[Campaign Filter]),SELECTEDVALUE(VizData[Message]),,Blank())
RETURN
If(searchvalue>0,"Y")

 

This works great - I simply use this as a visual-level filter in my table and tell it check it for 'Y'. I've been trying to extend this measure out to work in charts and cards, but for some reason (and quite randomly) some visualisations can't handle the measure. I'm thinking the best thing to do is try and essentially create a new column that checks if anything in the Campaign Filter[Campaign Filter] appears in each VizData[Message].

 

As an example. here is my Campaign Filter table (in reality this contains 35+ hashtags to potentially filter against):

 

Campaign Filter
#BuyCandy
#BananasAreGreat
#EatCarrots

 

And here it the VizData table - I want to search [Message] for anything in the above table and return it in the [Campaign] column if there is a match:

 

MessageCampaign
Next week is a big week for restaurants in the area! 
We've just been to the Candy Store and bought treats #BuyCandy#BuyCandy
Once you #EatCarrots fresh out the ground you won't go back to shop bought#EatCarrots
We ate too much #CandiedSalmon and now we feel sick 
Which is the greatest apple of all time - you decide! 
We're celebrating #BananasAreGreat by buying some from the grocery store!#BananasAreGreat

 

Does anyone have any idea of how I might do this...or if there is a way of getting my measure to work in cards, etc?

 

Thanks in advance,

 

Sam

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Check if anything from a list appears in a text string

Hi,

 

Try this calculated column formula

 

=FIRSTNONBLANK(FILTER(VALUES(MatchList[Campaign Filter]),SEARCH(MatchList[Campaign Filter],VizData[Message],1,0)),1)

 

Hope this helps.

8 REPLIES 8
Community Support Team
Community Support Team

Re: Check if anything from a list appears in a text string

Hi @SamBerger,

 

By my test with your measure, it seems that we could use this measure in card visual.

 

card.PNG

 

If you still need help, could you share some screenshots to specify your problem?

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SamBerger Regular Visitor
Regular Visitor

Re: Check if anything from a list appears in a text string

Hi @v-piga-msft,

 

Thanks for your reply - one thing I forgot to mention in my original post was that the cards are numerical values that need filtering once the Campaign Filter has been selected and this appears to be where the problem is. Below is the dashboard I'm working on:

 

dashboard.png

 

So I'm trying to filter (purple arrow) the 'Comments' card (red arrow) and I have the 'FB_Matched' measure (the same as the VizData measure in my original post, green arrow) (the same code you've been experimenting with) in the Visual Level filter. However, it won't let me choose 'contains' 'Y' - I can't click into the field to enter anything. Hence why I think maybe a calculated column is going to be the way forward.

 

Thanks!

 

Sam

Community Support Team
Community Support Team

Re: Check if anything from a list appears in a text string

Hi @SamBerger,

 

If your two tables without any realtionship, we only could try this way below.

 

Create a calculated table with CROSSJOIN.

 

Table = CROSSJOIN('Campaign Filter table','VizData')

Then create the calculated column with SEARCH function.

 

Column = IF( SEARCH('Table'[Campaign Filter],'Table'[Message],,0),'Table'[Campaign Filter])

Then filter the value <>0.

 

Capture.PNG

 

Two points you need to note when you try this way.

1.You need to use the latest version of Power BI Desktop for the new feature filter the column.

2.CROSSJOIN is not good for the large data model, it will show slow performance.

 

More details, you could have a reference of this attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SamBerger Regular Visitor
Regular Visitor

Re: Check if anything from a list appears in a text string

Thanks @v-piga-msft - this looks good, One question: I downloaded your pbix and it's saying I need the latest version - I have the June 2018 version installed and can't see a July 2018 release to download. Which version are you using?

 

Cheers,

 

Sam

Community Support Team
Community Support Team

Re: Check if anything from a list appears in a text string

Hi @SamBerger,

 

I used the Power BI Desktop( Version 2.60.5169.2101 64-bit (July 2018) ) downloaded from Microsoft App Store.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Check if anything from a list appears in a text string

Hi,

 

Try this calculated column formula

 

=FIRSTNONBLANK(FILTER(VALUES(MatchList[Campaign Filter]),SEARCH(MatchList[Campaign Filter],VizData[Message],1,0)),1)

 

Hope this helps.

SamBerger Regular Visitor
Regular Visitor

Re: Check if anything from a list appears in a text string

Thank you @Ashish_Mathur - that's working perfectly. Thanks to @v-piga-msft for your assistance too!

Super User
Super User

Re: Check if anything from a list appears in a text string

You are welcome.