Forum Discussion

philjohn's avatar
philjohn
Frequent Visitor
3 years ago
Solved

One Slicer searches Multiple Rows

Building a Staff Organisation dashboard, and we want to be able to filter by Staff Expertise Some staff have multiple expertise  A messy slicer with comma delimited expertise would technically work...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    You can try something like the following...
    I made a small sample dataset

    Create a calculated table with,

    Exp List = 
    var _vTable =
    UNION(
        SELECTCOLUMNS('Table', "Experience", 'Table'[Exp1]),
        SELECTCOLUMNS('Table', "Experience", 'Table'[Exp2]),
        SELECTCOLUMNS('Table', "Experience", 'Table'[Exp3])
    )
    Return
    FILTER(
        DISTINCT(_vTable),
       [Experience]<> ""
    )

    To get a distinct list of non-blank experience.

    Create a measure,

    Measure = 
    var _expTable =
    UNION(
        SELECTCOLUMNS('Table', "_exp", SELECTEDVALUE('Table'[Exp1])),
        SELECTCOLUMNS('Table', "_exp", SELECTEDVALUE('Table'[Exp2])),
        SELECTCOLUMNS('Table', "_exp", SELECTEDVALUE('Table'[Exp3]))
    )
    Return
    COUNTROWS(INTERSECT(_expTable, 'Exp List'))

    Create a table visual (or matrix should also work) with your values.

    Put the measure in the filter pane under 'Filters on this Visual' (for the table you created) and set the filter value to greater than 0

     

     Create a slicer with the values from the calculated table and you should now be able to slice by experience.

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to go about by creating a slicer which allows users to choose either exp 1/2/3.
Then this selection only returns a table with that specific column . Eg if user selects exp 1 . A table is return with distict values of 1 . Then this can be added to the text filter search ? 

How to go about this ?