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.

     

jgeddes's avatar
jgeddes
Super User
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.