Filter table having a column with delimited values

[Drake1002]

Hi,

I hope someone can help me with this.

I have a comma delimited column . I want to be able to see only those rows which contain the value selected in the slicer .

E.g. if some one selects apple then rows containing apple will be diplayed

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Try to put the below measure into the visual filter pane.

condition measure: = 
VAR _countslicer =
    COUNTROWS ( VALUES ( Slicer[Slicer] ) )
VAR _slicerselect =
    VALUES ( Slicer[Slicer] )
VAR _t =
    GENERATESERIES ( 1, _countslicer, 1 )
VAR _changefruittable =
    ADDCOLUMNS ( Fruits, "@path", SUBSTITUTE ( Fruits[Fruits], ", ", "|" ) )
VAR _pathlength =
    ADDCOLUMNS ( _changefruittable, "@length", PATHLENGTH ( [@path] ) )
VAR _generate =
    GENERATE ( _pathlength, GENERATESERIES ( 1, [@length], 1 ) )
VAR _fruitinfo =
    ADDCOLUMNS ( _generate, "@item", PATHITEM ( [@path], [Value], TEXT ) )
RETURN
    INT (
        COUNTROWS ( FILTER ( _fruitinfo, [@item] IN _slicerselect ) ) <= _countslicer && 
        COUNTROWS ( FILTER ( _fruitinfo, [@item] IN _slicerselect ) ) <> 0
    )

[Drake1002]

Thanks Jihwan_Kim. This works when the selected value is at the first index for the string otherwise it displays nothing.

[PhilipTreacy · Accepted solution]

Hi @Drake1002 

Download PBIX file with example shown below

Create a table with the fruit names which will be used for the slicer.  This is not connected to the Fruits table.

Create this measure in the Fruits table

Selected Fruit = IF(CONTAINSSTRING(MAX('Fruits'[Fruits]), SELECTEDVALUE(SlicerTable[Fruit])), 1)

In the table visual for the Fruits table, add that measure as a filter and set it to Show items when the value is 1

Regards

Phil

[Drake1002]

Thanks PhilipTreacy. This works well if for single selection what if I want multiple selection .Also the table displays total at the end but does it not the actual count. How to find the actual count and display it on the card.

For example, if apple and pear is selected then the rows with apple and pear should be displayed.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Try to put the below measure into the visual filter pane.

condition measure: = 
VAR _countslicer =
    COUNTROWS ( VALUES ( Slicer[Slicer] ) )
VAR _slicerselect =
    VALUES ( Slicer[Slicer] )
VAR _t =
    GENERATESERIES ( 1, _countslicer, 1 )
VAR _changefruittable =
    ADDCOLUMNS ( Fruits, "@path", SUBSTITUTE ( Fruits[Fruits], ", ", "|" ) )
VAR _pathlength =
    ADDCOLUMNS ( _changefruittable, "@length", PATHLENGTH ( [@path] ) )
VAR _generate =
    GENERATE ( _pathlength, GENERATESERIES ( 1, [@length], 1 ) )
VAR _fruitinfo =
    ADDCOLUMNS ( _generate, "@item", PATHITEM ( [@path], [Value], TEXT ) )
RETURN
    INT (
        COUNTROWS ( FILTER ( _fruitinfo, [@item] IN _slicerselect ) ) <= _countslicer && 
        COUNTROWS ( FILTER ( _fruitinfo, [@item] IN _slicerselect ) ) <> 0
    )