Filter slicer using multiple selections that are substrings

[Anonymous]

Hi,

I am trying to build a slicer that allows multiple selections from dropdown which then filters out the dataset based on the selections.

getting a single selection was quite easy by using:

MultiSelect =

VAR searchvalue=SEARCH(SELECTEDVALUE('Product'[Products]),SELECTEDVALUE(ScenarioResults[SolutionProducts]),,Blank())

return

If(searchvalue>0,"Found")

Now I am aware that SELECTEDVALUE is not suitable for the multi select, but so far I cant get it to run with options like CONCATENATEX.

The issue that I have is that my Product table is a single column table with a plain list of products each on an inidividual row and this is static.

my ScenarioResults[SolutionProducts] column contains strings that contain multiple products

for example:

product table has values 1-10 each on its own row

my SolutionProducts column might have something like:

1,2,4,5

1,2

1,6,8,9,10

1,2,4,6,7,8,9,10

the single search nicely lets me select 9 from the dropdown and my report updates to only show:

1,6,8,9,10

1,2,4,6,7,8,9,10

selecting a second value then shows all the data. When trying to use CONCATENATEX I could not get it to filter at all.

VAR _Count = COUNTROWS(VALUES('Product'[Products]))
VAR _Concat = CONCATENATEX(VALUES('Product'[Products]),'Product'[Products],",")
RETURN IF(_Count>2,"Multiple Selection",_Concat)

this did nothing since it could not compare to my ScenarioResults[SolutionProducts] column

I got the feeling I am either missing something really obvious or have gone completely down the wrong path. Any help much appreciated here

[MFelix]

Hi @Anonymous ,

One option can be to use the following code:

Product selected exists = IF(
      SUMX(Products;
           FIND(
                UPPER(Products[Product]);
                UPPER(SELECTEDVALUE(Solution_Products[SolutionProducts]))
                ;;0
               )
          ) > 0;
      "Yes";
      "No"
     )

Has you can see below the values are filtered out:

You can now use the measure has a filter on your visualizations you can also replace the No by blank.

PBIX file attach.

[Anonymous]

Hi Miguel,

Thank you for this. This is definitely a lot closer. 

thre is 1 thing that I need to change on here. Your code filters based on 'OR' so that if I select 4 and 6, rows 2,3,and 4 appear. However, I need the filter to be an 'AND' so that only row 3 should say "Yes"

Many thanks,

Marc

[MFelix · Accepted solution]

Hi @Anonymous ,

In this case you need to do a different setup use the following measure:

Product selected exists = 
VAR SplitByCharacter = ","
VAR ProductTableSelection =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                SELECTCOLUMNS (
                    Solution_Products;
                    "Find_Text"; Solution_Products[SolutionProducts]
                );
                VAR TokenCount =
                    PATHLENGTH ( SUBSTITUTE ( [Find_Text]; SplitByCharacter; "|" ) )
                RETURN
                    GENERATESERIES ( 1; TokenCount )
            );
            "Product_selection"; PATHITEM ( SUBSTITUTE ( [Find_Text]; SplitByCharacter; "|" ); [Value] )
        );
        "Products_Selected"; [Product_selection]
    )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALLSELECTED ( Products );
                FORMAT ( Products[Product]; "#" ) IN ProductTableSelection
            )
        )
            = COUNT ( Products[Product] );
        "Yes";
        "No"
    )

[Anonymous]

Hi Miguel,

That is perfect thank you. 

Marking as solved. 

Can have default showing by adding another if to allow for show all when nothing selected.

To do that, I added another coutrows section but instead of having 

= COUNT ( Products[Product] );

I added

> 1;

[MFelix]

Hi @Anonymous ,

you can ue isfiltered syntax see formula adjusted below:

Product selected exists = 
VAR SplitByCharacter = ","
VAR ProductTableSelection =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                SELECTCOLUMNS (
                    Solution_Products;
                    "Find_Text"; Solution_Products[SolutionProducts]
                );
                VAR TokenCount =
                    PATHLENGTH ( SUBSTITUTE ( [Find_Text]; SplitByCharacter; "|" ) )
                RETURN
                    GENERATESERIES ( 1; TokenCount )
            );
            "Product_selection"; PATHITEM ( SUBSTITUTE ( [Find_Text]; SplitByCharacter; "|" ); [Value] )
        );
        "Products_Selected"; [Product_selection]
    )
RETURN
   IF(
       ISFILTERED(Products[Product]);
            IF (
                COUNTROWS (
                    FILTER (
                        ALLSELECTED ( Products );
                        FORMAT ( Products[Product]; "#" ) IN ProductTableSelection
                    )
                )
                    = COUNT ( Products[Product] );
                "Yes";
                "No"
            );
    "Yes"
    )