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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

URGENT HELP needed: search for multiple values from different lists

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:

Measure =
VAR _tbl1 = CALCULATETABLE(VALUES(brand[Brand]), FILTER(brand, FIND(brand[Brand],MAX(Table[Text]),1,0)>0))
VAR _tbl2 = CALCULATETABLE(VALUES(manufacturer[Manufacturer]), FILTER(manufacturer, FIND(manufacturer[Manufacturer],MAX(Table[Text]),1,0)>0))
VAR _tbl = UNION(_tbl1, _tbl2)
VAR _x = SUMX(_tbl, MAX(Table[Value])) / COUNTROWS(_tbl)
RETURN _x
 
The result is incorrect: 
a. Listed in a table with Date, Text, Value, Measure, is shows the wrong totals.
b. If I add another column say Measure2, for which Measure displays some empty rows, it doesn't show any total at all anymore (and it's no way to fix it by replacing the blanks with 0s). 
c. In a timeline barchart with Brand as legend and Manufacturer as filter is shows only the wrong totals and, depending on the selected Manufacturer, it might not aggregate up from day to month etc. at all or even more incorrectly.
 
I am unfortunately unable either to show the original tables and charts, due to compliance issues, and I also can't replicate it in a mock file, as it is pretty complicated and it woud take me the whole day. 
Even more unfortunately, the matter is urgent, I need to give in the report asap.
 
Can anyone please help? Many thanks in advance!
8 REPLIES 8
Anonymous
Not applicable

Here some mock data:

TextDate Value 
Apples' China risk9/8/2023      778,985
Pick Peanuts, Pick Apples and Get Out of Town10/1/2023 13,270,269
A Low-Cost Grocery Delivery Service With Much More Than Ugly Apples11/9/2023   5,543,076
Krispy Kreme urgently recalls four-pack of doughnuts over peanut allergy fears8/7/2023   4,013,349
Recall over allergy fears: Chocolate raisin snacks may contain peanuts6/9/2023   6,518,508
I have a severe allergy to strawberries9/23/2023   4,013,349
Aldi urgently recalls deli meats over allergy fears8/23/2023   4,013,349

 

The two keyword lists are:

CauseEffect
appleallergy
peanutrecall
raisin 

 

You can consider then the following measure syntax:

Measure =
VAR _tbl1 = CALCULATETABLE(VALUES(kw[Cause]), FILTER(kw,
CONTAINSSTRINGEXACT(MAX(Table[Text]),kw[Cause])=TRUE()))
VAR _tbl2 = CALCULATETABLE(VALUES(kw[Effect]), FILTER(kw,
CONTAINSSTRINGEXACT(MAX(Table[Text]),kw[Effect])=TRUE()))
VAR _tbl = UNION(_tbl1_tbl2)
VAR _x = SUMX(_tblMAX(Table[Value])) / COUNTROWS(_tbl)
RETURN _x
 
Cause and Effect appear as filters on the page, multiple selection allowed. 
I want to find the (selected) keywords from both these lists in Text and sum up the amounts Value corresponding to each found line that contains at least on of the selected keywords.
 
In a table or chart with Date, Text, Value, Measure I get wrong totals an no Totals (sums in the table). Of course the chart won't aggregate over Date.
 
I hope this helps. Many thanks!

See if this helps

lbendlin_0-1699887428505.png

 

Anonymous
Not applicable

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.

EmaT_0-1699890921570.png

 

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.

lbendlin_0-1699891857159.png

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

EmaT_0-1699945529076.png

Many thanks anyways!

 

lbendlin
Super User
Super User

We won't be able to help without sample data.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors