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

     

DTPBI's avatar
DTPBI
Frequent Visitor
2 years ago

Thanks again for your assistance. I managed to get it working based on my original post and modifying some of your logic using this measure:

Note: Fruits[AssignedCount] is a calculated column in the Fruits table to count number of assigend fruits per row.

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

I then add the above measure to the table as a filter and select show items where Is Not Blank.

I still have one issue, while this works perfectly when there are slections in the slicer, it does not display any rows in the table where no selections have been made in the slicer. I would like the table to display all rows in that sceanrio.
  • DTPBI's avatar
    DTPBI
    Frequent Visitor
    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