One Slicer searches Multiple Rows

[philjohn]

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 when using the search function, but isn't user friendly if scrolling and selecting. 

SO

I have 3 columns in the database - Expertise 1, Expertise 2, Expertise 3

Is there anyway that a slicer can take the uniques of all these 3 columns and turn it into a single filter list - without the heirachal steps

Thanks in advance 

[wizard23]

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 ?

[jgeddes · Accepted solution]

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.