Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter slicer using multiple selections that are substrings

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
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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"
    )

     

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        MFelix
        Super User
        5 years ago

        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"
    )