Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        tamerj1
        Community Champion
        3 years ago

        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. 

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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