Slicer filtering on a list inside a table

Question

assuming I have a table that has a list inside

+---------+--------------+
|   tag   |     val      |
+---------+--------------+
| [a,b,c] | 1            |
| [a,e]   | 2            |
| [f,g]   | 3            |
| [e,f]   | 4            |
+---------+--------------+

can I create a slicer that when selected will still filter the item inside the list of the tag column?

eg. i select on the filter "a" it will show 1 and 2. "e" will filter 2 and 4, "f" will filter 3 etc.

msta42a · Accepted Answer · 2021-06-24 07:50:21Z

1

You may create a measure (returning 1/0) where you use PATHCONTAINS function. We need to remove square bracket and replace commas to pipe "|"; This measure you can put to filter pane in table/matrix visualization https://dax.guide/pathcontains/

ForFilter = 
var __selectedTag = SELECTEDVALUE(disconnected[tagList])
var __tags = SELECTEDVALUE('Table'[Tag])
var __path = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(__tags,",","|"),"[",""),"]","")
return

IF(PATHCONTAINS(__path, __selectedTag),1,0)

EDIT: version for multiple selection

var __string = CONCATENATEX( VALUES(disconnected[tagList]), disconnected[tagList],"|")
var __tags = SELECTEDVALUE('Table'[Tag])
var __path = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(__tags,",","|"),"[",""),"]","")
VAR Table0 =
    SELECTCOLUMNS(
    TOPN(1,
    SELECTCOLUMNS(
    ADDCOLUMNS (
        GENERATE (
            ROW ( "tag", __path ,"Text",__string ),
            VAR TokenCount =
                PATHLENGTH ([Text] )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "Word", PATHITEM ([Text], [Value] )
        
    ),
    "Word",IF(PATHCONTAINS([tag],[Word]),1,0),
    "Tag", [tag],
    "Values", [Value]
    ), [Word],DESC, [Values])
    ,"Bool", [Word])
    return

    Table0

edited Jun 24, 2021 at 7:50

answered Jun 23, 2021 at 8:05

msta42a

3,7491 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Led Over a year ago

This actually works when we select 1 item from the disconnected list of tags, when I tried multiple tags or select "all values" it does not work anymore

Led Over a year ago

ok one last thing, will it work on bar charts?

msta42a Over a year ago

As i know answer is yes (but i never need this kind of solution in my envoirment)

Collectives™ on Stack Overflow

Slicer filtering on a list inside a table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related