Use a slicer to filter based on keyword table

[GertB]

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 

[GertB]

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). 

  

[GertB]

Hi Pete, 

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

Gert

[BA_Pete · 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

[BA_Pete]

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