Forum Discussion

DTPBI's avatar
DTPBI
Frequent Visitor
2 years ago
Solved

How to filter table where a column has multiple comma delimited values

Hi,

Hoping someone can help whether this scenario is possible....and if so how to achieve it.

I have a table that has comma seperated values in a single column (see Fruits table in Red below). I want to be able to filter the table for rows that exactly match/contain the values selected in the slicer (slicer in Blue below). E.g if someone selects Apple & Pear in the slicer then row 3 will be the result in the Fruits table.

 

Need Help
  • DTPBI's avatar
    DTPBI
    2 years ago

    Thanks but this didn't work as expected. When selecting all options it showed all rows when it should only show rows if all options were assigned in the Fruits column (comma seperated) to the row.

    However, I have managed to work it out.


    TABLES


    MEASURE

    Measure =
    VAR selValues = CONCATENATEX(VALUES(Slicer[Fruit]),Slicer[Fruit],", ",Slicer[Fruit],ASC)
    VAR selCount = COUNTROWS(ALLSELECTED(Slicer[Fruit]))
    VAR sTableselCount = CALCULATE(COUNTROWS(Table),Table[AssignedCount]=selCount)
    VAR sTableselValues = CALCULATE(COUNTROWS(Table),Table[Fruits]= selValues)
    VAR sResults = IF(sTableselCount>0 && sTableselValues >0,1)
    RETURN
    IF(NOT ISFILTERED(Slicer[Fruit]),0,sResults)



    Add the measure to the table filter as below

     

10 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    2 years ago

    Hi DTPBI ,

     

    Try the following measure:

    Filter Measure = 
    IF( SUMX(Slicer,
        FIND(
            Slicer[Fruit],
            MAX(Fruits[Fruits]),,0)) > 0, 1)

    Then use this one on the table visualization has a filter.

     

     

     

    • DTPBI's avatar
      DTPBI
      Frequent Visitor
      2 years ago

      Thanks for the quick response. I may not have been very clear in my original post. I need only results that match exactly what has been selected in the slicer. So for your exampe of Apple & Pear selected, only the row Apple, Pear should be returned and not other rows that contain ether Apple or Pear.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DTPBI ,

    First we can start by creating such a table in PowerQuery:

    And then create relationship with slicer table:

    Create a new measure like:

     

    Measure = 
VAR __cur_fruit = SELECTEDVALUE('Table1'[FRUITS])
VAR __fruits = VALUES('Table2'[FRUITS])
VAR __slicer_count = COUNTROWS(ALLSELECTED('Slicer'[SLICER]))
VAR __table_count = CALCULATE(COUNTROWS('Table2'),'Table2'[FRUITS] IN __fruits && 'Table2'[FRUITS]=__cur_fruit)
VAR __result = IF(__cur_fruit IN __fruits && __slicer_count = __table_count,1)
RETURN
__result

     

    And apply it to the visual's filter:

    Output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

    • DTPBI's avatar
      DTPBI
      Frequent Visitor
      2 years ago

      Thanks for the quick response. I may not have been very clear in my original post. I need only results that match exactly what has been selected in the slicer. So for your exampe of Apple & Pear selected, only the row Apple, Pear should be returned and not other rows that contain ether Apple or Pear.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi DTPBI ,

        Please try this measure:

        Measure = 
VAR __cur_fruit = SELECTEDVALUE('Table1'[FRUITS])
VAR __fruits = VALUES('Table2'[FRUITS])
VAR __slicer_count = COUNTROWS(ALLSELECTED('Slicer'[SLICER]))
VAR __table_count = CALCULATE(COUNTROWS('Table2'),'Table2'[FRUITS] IN __fruits && 'Table2'[FRUITS]=__cur_fruit)
VAR __table_count_2 = CALCULATE(COUNTROWS('Table2'),'Table2'[FRUITS]=__cur_fruit,ALLEXCEPT('Table2','Table2'[FRUITS]))
VAR __result = IF(__cur_fruit IN __fruits && __slicer_count = __table_count && __slicer_count = __table_count_2, 1)
RETURN
__result

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group