Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count items from a splitted string

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

 

DepartmentStaffSkills
AJohnAI, ML
ALilyJava, Angular, Cloud
BMaxJava, Angular
CRobertMobile
DTomReact JS
ECristJava, Angular, ReactJs, Python, Jenkins
FTaarAngular, NodeJs
5 REPLIES 5
tamerj1
Super User
Super User

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
Not applicable

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

@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
Not applicable

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 SkillsHeadcount
AAI1
AML1
AJava1
AAngular 1
ACloud1
BJava1
BAngular 1
CMobile1
DReact JS1
FreemanZ
Super User
Super User

hi @Anonymous 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors