The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have two lists of keywords: Brand and Manufacturer. I have filters allowing to select multiple out of both lists.
I need to find the (selected) keywords from (both) these lists in a table column Text and sum up the amounts Value corresponding to each found line that contains at least on of the selected keywords.
I am currently using a measure:
Here some mock data:
Text | Date | Value |
Apples' China risk | 9/8/2023 | 778,985 |
Pick Peanuts, Pick Apples and Get Out of Town | 10/1/2023 | 13,270,269 |
A Low-Cost Grocery Delivery Service With Much More Than Ugly Apples | 11/9/2023 | 5,543,076 |
Krispy Kreme urgently recalls four-pack of doughnuts over peanut allergy fears | 8/7/2023 | 4,013,349 |
Recall over allergy fears: Chocolate raisin snacks may contain peanuts | 6/9/2023 | 6,518,508 |
I have a severe allergy to strawberries | 9/23/2023 | 4,013,349 |
Aldi urgently recalls deli meats over allergy fears | 8/23/2023 | 4,013,349 |
The two keyword lists are:
Cause | Effect |
apple | allergy |
peanut | recall |
raisin |
You can consider then the following measure syntax:
Many thanks!
But I want it to show me texts with all selected keywords, no matter which lists, with multiple selection allowed.
E.g. in image it should only show the last text, "Recall over allergy fears..."
And it also doesn't show any totals, the same problem I had and which makes any aggregeation (e.g. over date) impossible.
Your request is ambiguous. Are you saying you want to combine the list filters in an AND fashion rather than OR fashion?
Show =
if(ISFILTERED(Causes[Cause]),var a = ADDCOLUMNS(values(Causes[Cause]),"pos",search([Cause],SELECTEDVALUE('Table'[Text]),1,0)) return sumx(a,[pos]),1) *
if(ISFILTERED(Effects[Effect]),var a = ADDCOLUMNS(values(Effects[Effect]),"pos",search([Effect],SELECTEDVALUE('Table'[Text]),1,0)) return sumx(a,[pos]),1)
My version shows the totals - not sure what you have changed.
Yes, exactly, combine filters in an AND fashion.
So, if raisin AND allergy AND recall are selected, it shows exactly the text you have in your image, and which is now shown if only raisin but none of the Effect filters.
And true, if only raisin and no Effect filter is chosen, it already shows what I need.
I also forgot to mention that I was using CONTAINSEXACTSTRING as I really need to search for full words, also case-sensitive. But of course I can change this myself.
Thanks a lot!
So, if raisin AND allergy AND recall are selected, it shows exactly the text you have in your image, and which is now shown if only raisin but none of the Effect filters.
That is yet another rule that you didn't make clear. So not only combine the categories but also demand that all selected values in a category are present? You would need to replace SUMX with PRODUCTX.
if you want to use CONTAINSEXACTSTRING then you would have to map True() to 1 and False() to 0.
I was trying to specify right from the start that I need multiple selections and select all.
I'll see what's possible with PRODUCTX.
Another issue is that even using your measure as developed (with or), it won't aggregate or even show all days correctly. In the picture below it shows only the 7th of Jan, nothing else.
This was also one of the issues I was initially writing about.
I don' know why it won't let me attach the file (type .pbix not supported)!! I used drag&drop, is there another method? I just added a Date Hierarchy.
Many thanks anyways!
We won't be able to help without sample data.