How to show results against three columns using same slicer in the same visuals

[HassanAshas]

Hello, I have the dataset like this

Emp Code	Name	Skill	Grade
1	Matt	AWS, Python, Django	5
2	Avery	AWS	4
3	Aryub	AWS, Python, JavaScript	6
4	Jacob	Azure, NodeJS	5
5	Zara	NodeJS, React	5
6	Ali	Azure, Python, PySpark	1
7	John	PySpark	5
8	Alisha		3
9	Novak	Python	6
10	Alex	Django	4
11	Emma	JavaScript, React, NodeJS	4

I need to work with the Skills of the people but I am getting multiple values against them (I do know however that maximum values of Primary Skill by user will be three) 

So to work with individual skills, I have for now created three columns from the one column, with the use of split by delimeter. 

I have three visuals as shown below,

My problem is I need to use one single slicer of Skills for these three visuals.

Also, I want to use the values of these three columns as "one". That is, when I am counting Employees then I want to count the SUM of all three columns against each Skill. 
That is, if Python exists 3 times in "Skill.1", 2 times in "Skill.2", 2 times in "Skill.2" - then total Employees with Python will be 7. (It is also guaranteed that no employee will have a douplicate skill) 

Can anyone help me out in achieving these two requirements? (Using one slicer for all three slicers, and using sum of all three columns in the Resultant visuals) 

You may download the Power BI from here if needed: https://drive.google.com/file/d/1G1fWEkuQDqcgUnPTsmcGWu2fvoseUh26/view?usp=sharing

[olgad · Accepted solution]

Hi, my suggestion to you, dont split the skills into columns, split into rows.

After you splitted right click on the skill column, transform-trim, to get rid of all the spaces. filter out blanks (for those who didnt have the 3rd or the second skill) and that solves both of your problems. 

[HassanAshas]

@olgad  Employees table is a Dimension table in my data model which is connected to the Fact Table with the Emp_ID Key. 

If I duplicate the rows in my Employees table, then I will have duplicate Emp IDs for all the employees. This will be an issue because it will then create a Many-to-Many relation between the two tables. That's why I don't want to split by Rows. 

I am quite new to Power BI so I am not entirely sure how can I handle such a scenario. 

[olgad]

Create another employee table only with employees, duplicate the original one and leave only employee column. Then create relationship from that table one to many to this "employee skill" table and to the other fact table you have. Use employee from the newly created table in your visuals

[olgad]

@HassanAshas Hi, just wanted to follow up if you tried out my second suggestion and resolved the issue?

[DOLEARY85]

Hi,

you could select all 3 split skills columns and unpivot them in power query, this would combine them into one column.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[HassanAshas]

Hi, that doesn't work for me. Because it will create a Many-to-Many relation with my Fact Table (due to the duplication of Employees) 

I want to avoid the creation of Many to Many relation.