Count items from a splitted string

[Anonymous]

Hello,

I have some ideas for my report, but sadly, with my limited capability, I cannot visualize it. I want to count the skills most popular for each department and have a bar chart for that. The original data structure is in the below form. 

I would like to somehow visualize/count the number of staff in department A has skill AI and all that. 

Your help is much appreciated. 

Thank you 

Best,

ihaveapaincake

Department	Staff	Skills
A	John	AI, ML
A	Lily	Java, Angular, Cloud
B	Max	Java, Angular
C	Robert	Mobile
D	Tom	React JS
E	Crist	Java, Angular, ReactJs, Python, Jenkins
F	Taar	Angular, NodeJs

[tamerj1]

Hi @Anonymous 

let's say that you have a disconnected table 'Skills' that contains all the skills. Such filter/slicer table would be required. You can create manually or using dax (let me know if you need help in that). 
now you can create a measure as follows 

Count =
COUNTROWS (
FILTER ( 'Table', CONTAINSSTRING ( 'Table'[Skills], MAX ( Skills[Skills] ) ) )
)

Then place the measure in the y-axis of a stacked bar chart the drag 'Table'[Department] to x-axis and Skills[Skill] into the legend 

[Anonymous]

Hello,

That would be nice if you would suggest me how to create the Skills table. I am a novive with PowerBI and DAX, so your help is much appreciated.

Also, can I ask why we use MAX(Skills[Skills]) for counting?

And I do not get the concept of creating measure. How will Power BI process when you stack the measure, skills and department name into such chart?

Thanks a lot!!!!!!!!!!!!!!!!!!!!!

[tamerj1]

@Anonymous 

You can manually insert it or just type it in excel and then import it. Please let me know if you're still facing problems. 

[Anonymous]

I mean that all skills could be detected and counted for every department. For example, the result desired would be as follow: (headcount value would be larger than 1 if that department has 2 or more members knowing that skill)

Please tell me if it is still unclear

Department	Skills	Headcount
A	AI	1
A	ML	1
A	Java	1
A	Angular	1
A	Cloud	1
B	Java	1
B	Angular	1
C	Mobile	1
D	React JS	1

[FreemanZ]

hi @Anonymous 

what do you mean by "all that" in "skill AI and all that. "?