Forum Discussion
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:
| Message | Campaign |
| 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
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
- Ashish_Mathur
Super User
Hi,
Try this calculated column formula
=FIRSTNONBLANK(FILTER(VALUES(MatchList[Campaign Filter]),SEARCH(MatchList[Campaign Filter],VizData[Message],1,0)),1)
Hope this helps.
Thank you Ashish_Mathur - that's working perfectly. Thanks to v-piga-msft for your assistance too!
- Ashish_Mathur
You are welcome.
v-piga-msftResident Rockstar
Hi Anonymous,
By my test with your measure, it seems that we could use this measure in card visual.
If you still need help, could you share some screenshots to specify your problem?
Best Regards,
Cherry
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:
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
- v-piga-msft
Hi Anonymous,
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.
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