Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi,
Try this calculated column formula
=FIRSTNONBLANK(FILTER(VALUES(MatchList[Campaign Filter]),SEARCH(MatchList[Campaign Filter],VizData[Message],1,0)),1)
Hope this helps.
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!
You are welcome.
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
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
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
Hi @Anonymous,
I used the Power BI Desktop( Version 2.60.5169.2101 64-bit (July 2018) ) downloaded from Microsoft App Store.
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |