Multi-Select filtering column checking if it contains multiple pieces of text

[morgtd30]

I've tried using this solution, but it seems to work on an OR basis
https://community.powerbi.com/t5/Desktop/Dynamic-filtering-slicing-if-text-contains/td-p/425504

I'd like to achieve the following:

Data:

Date         Text
2018-11-01   Apples
2018-04-03   Apples; Oranges; Grapes
2018-02-01   Apples; Oranges
2018-01-03   Oranges; Grapes; Bananas

Slicer:

[  ] Apples
[ x ] Oranges
[ x ] Grapes
[  ] Bananas

Should filter to orders which included Oranges AND Grapes, but could have include other items as well:

Date         Text
2018-04-03 Apples; Oranges; Grapes
2018-01-03 Oranges; Grapes; Bananas

Thank you!

[v-yetao1-msft]

Hi @morgtd30 

(1)Create a table with selection “Apples”,” Oranges”,” Grapes”,” Bananas”.

(2)Add a slicer with [Slicer] .And then create a measure to return the string of select value .

Measure 2 = CALCULATE(CONCATENATEX(VALUES(Slicer[Slicer]),Slicer[Slicer],"; "))

(3)Create a measure to judge whether the Measure 2 in the column [Text] .If yes ,return 1 , otherwise return 0 .

CONTAINSSTRING = IF(CONTAINSSTRING(SELECTEDVALUE('Table'[Text]),Slicer[Measure 2]),1,0)

(4)Put the CONTAINSSTRING measure in visual filter and set CONTAINSSTRING is equal to 1 .The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @v-yetao1-msft ,

Do you realize that your solution makes that if the order of the words are different then it does not return any result?

I have added a line with different values and the result is below:

Has you can see in your solution you are missing one line.

This is happening because using the CONCANATEX you are forcing the order of the text using my solution since I'm looking at individual words it shows the result:

[MFelix]

Hi @morgtd30 ,

I made a similar approach to this on this post:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Find-Words-on-sentence-from-another-selected...

Making some small adjustment I was abble to reach the following measure:

Find Word Formula_V2 = 
// Character that split phrase into words
VAR SplitByCharacter = " " 

// Temporary table that splits selected phrase into words
VAR Words_table =
   
        ADDCOLUMNS (
            GENERATE (
                SELECTCOLUMNS (
                    ALLSELECTED (Slicer[Slicer] ),
                    "Find_Text", Slicer[Slicer]
                ),
                VAR TokenCount =
                    PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) )
                RETURN
                    GENERATESERIES ( 1, TokenCount )
            ),
            "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] )
        )
RETURN
    IF (
// Function that returns the sum of the values of the words found in the sentance
        SUMX (
            Words_table,
            FIND (
// additonal spaces in the beginning and ending of each word allows to find the exact match, also removed the last "." so that would be specific words
                 UPPER ( [Word] ) & " ",
                 UPPER (  SELECTEDVALUE ( 'Table'[Text] ) ) &" ",
                ,
                0
            )
        ) > 0,
        1,
        BLANK ()
    )

See result below and in attach PBIX file:

If you want you can set the measure on the visualization filter pane and set it to non blank values.

[JeBro]

Hi,

When only Apples is selected, you expect that the table give 3 rows as a result.no result is showing in the table. But in stead only the row with only apples shows up. 

[MFelix]

The expected result is 3 rows because we are looking for a word inside a frase not a full match

[JeBro]

Hi,

Thanks, but what i ment is my result is 1 instead of the expected 3 with your example.

[MFelix]

Hi @JeBro 

Are you using the same example as mine or is your different? Are you using the sample file I provided?

[JeBro]

Hi, yes, i used the one provided by you

[MFelix]

Hi @JeBro ,

Have you made any change to the measures or model? I just downloaded the file and all works correctly:

[DebLee]

Hi MFelix! I stumbled across your post while looking for a measure to solve an issue I'm facing in PowerBI. 

From your last message, do you have a measure that when Apples and Oranges are selected from the slicer, only row 01/02/2018 appears, and when Apples, Oranges and Grapes are selected fromt he slicer, only row 03/04/2018 appears. 

But when only Apples are selected on the slicer, then all three rows will appear. 

Thanks in advance!