topic Re: Count items from a splitted string in DAX Commands and Tips

Count items from a splitted string

Anonymous — Fri, 10 Mar 2023 07:19:37 GMT

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

Re: Count items from a splitted string

FreemanZ — Fri, 10 Mar 2023 07:42:38 GMT

hi Anonymous

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

Re: Count items from a splitted string

Anonymous — Fri, 10 Mar 2023 09:14:41 GMT

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

Re: Count items from a splitted string

tamerj1 — Fri, 10 Mar 2023 09:32:41 GMT

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

Re: Count items from a splitted string

Anonymous — Mon, 13 Mar 2023 02:51:52 GMT

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!!!!!!!!!!!!!!!!!!!!!

Re: Count items from a splitted string

tamerj1 — Mon, 13 Mar 2023 03:19:18 GMT

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.