cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Use a slicer to filter based on keyword table

Hi,

 

I'm an newbee and need help. 

I have two tables:

1. Skills : skills of employees): simply one column with all skills.

2. Employee: a table with employee data containing one column with their skills (comma separated).  

 

I would like to create a visual table where we can filter based on skills from using a slicer where we can select one or more skills. i've checked several threads, but have not find an answer and simply do not know where to start.

 

Any suggestions ? 

 

Gert 

1 ACCEPTED SOLUTION

@GertB ,

 

Ok, try this.

 

Make sure your Skills and Employes tables are UNRELATED i.e. delete any relationships between them if they exist (you probably created one trying the last solution).

 

Then create this measure:

skillExists =
SEARCH(
    SELECTEDVALUE(Skills[Skills]),
    SELECTEDVALUE(Employees[Skills]),,
    -1
)

 

Apply this measure as a visual-level filter to your Employees table visual. Set the filter criteria to 'is greater than or equal to 1'.

 

Use your Skills[Skills] field in the slicer and this should filter any employees in the table visual that have that skill.

 

Obviously I've not tested this as I don't have your data, but let me know how it goes.

 

Pete

View solution in original post

4 REPLIES 4
Frequent Visitor

Hi, thanks for the reply, but that does not work because all the employee skills are in one column. see screenshot (in dutch, but i think it's pretty clear what i mean). What i need is that the visual table presents all employees with one or more skills that i have selected with a slicer. I have a complete list that holds all skills (no differences in spelling or capitals etc, it's a download from a system). 

 

  screenshot.PNG

Frequent Visitor

Hi Pete, 

Thanks very much for your help. That did the job ! 

Gert

@GertB ,

 

Ok, try this.

 

Make sure your Skills and Employes tables are UNRELATED i.e. delete any relationships between them if they exist (you probably created one trying the last solution).

 

Then create this measure:

skillExists =
SEARCH(
    SELECTEDVALUE(Skills[Skills]),
    SELECTEDVALUE(Employees[Skills]),,
    -1
)

 

Apply this measure as a visual-level filter to your Employees table visual. Set the filter criteria to 'is greater than or equal to 1'.

 

Use your Skills[Skills] field in the slicer and this should filter any employees in the table visual that have that skill.

 

Obviously I've not tested this as I don't have your data, but let me know how it goes.

 

Pete

View solution in original post

Super User II
Super User II

Hi @GertB ,

 

This depends on whether you want to be able to display skills that are not currently held by any employee or not.

 

If not, then you can just use the [Skills] field from your Employees table in a slicer. This will give you the option to filter by any skill that at least one employee currently holds.

 

If you need to be able to show skills gaps (assuming that your Skills table contains every possible skill, regardless of whether they are currently held by any employees or not, and that the values match exactly in terms of spelling to those found in the Employees table), then you would need to create a relationship between your Skills and Employees tables.

In the data model screen, drag Skills[Skills] over to Employees[Skills]. This should create a relationship where the Skills table shows as the ONE side, and the employees table shows as the MANY side.

 

Once this relationship is made, you can then use Skills[Skills] in any slicers/filters and, based on the assumptions above, this will allow you to select skills in the slicer that no employees currently hold in addition to those that they do.

 

Pete

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors