Forum Discussion
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 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
You can try something like the following...
I made a small sample datasetCreate 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.
2 Replies
You can try something like the following...
I made a small sample datasetCreate 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.
