Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
philjohn
Frequent Visitor

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 

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

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

jgeddes_0-1676045713831.png

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

jgeddes_1-1676046016751.png

 

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

jgeddes_2-1676046098833.png

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
wizard23
Frequent Visitor

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
Super User
Super User

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

jgeddes_0-1676045713831.png

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

jgeddes_1-1676046016751.png

 

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

jgeddes_2-1676046098833.png

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.