Count words from a list without losing dependencies

[airielo]

I am trying to visualize which competences are associated with each role, based on the frequency of these competences. How do I do this?

I am new to PowerBI so I have hard time coming up with a DAX solution on the stop. Right now I have a Wordcloud which is very unrepresentative and ideally I would like to have a bar chart.

I have a table which includes a bunch of information about different roles, a short excerpt is provided below. 

Role	Key competence
Developer	C#, Unity
Data Scientist	python, databases, sql, pandas, tensorflow

Thanks beforehand!!

[dax]

Hi airielo,

I am not clear about your requirement, did you want to get the count of Key competence in each role? If so, you could try to use below measure 

Count comma = LEN(MIN('Table'[Key competence]))-LEN(SUBSTITUTE(MIN('Table'[Key competence]),",",""))+1

Or try below M code to create a custom column like below

List.Count(Text.Split([Key competence],","))

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[airielo]

Thanks Zoe, appreciate your willingness to help!!!

This is unfortunately not what I was looking for. I am interested to retain both the name of the competency + its count.

Please see my answer below (with pictures) to see what result I would want to get.

Thanks!

[dax · Accepted solution]

Hi airielo,

I am not very clear about your requirement? Did you mean that when you have table like below, you want to calculate the percentage of each language in each role?

If so, you could try to convert table like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQqoLMnIz9NRMDewVgjSUTADUsGBPjoKpgZKsTrRSklAJc7KEGmvxLLE4OSizIISiDqYVpDSWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"amount", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "amount", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"amount.1", "amount.2", "amount.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"amount.1", type text}, {"amount.2", type text}, {"amount.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"name"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Int64.Type}})
in
    #"Changed Type2"

By the way, I don't understand your graph 2, if possible, could you please inform me more detailed information(such as your sample data and your expected output or you could explain the logic to me)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daviejoe]

I'm not 100% sure on what you're exactly needing but if you wanted to created measures that count the various different strings you could try creating the below measure to count instances of python being listed as a skill/competence

PythonCount = CALCULATE( COUNTROWS(your_table_name), SEARCH("python",your_table_name[Key competence],,0) >0)

[airielo]

Hey and thank you for your engagement! The thing is, I dont know the list of competences beforehand, since they are user-input, therefore it wont work just making measures for each specific skill.

Below is what my data looks like now, Powerbi is treating the whole string like a single input, while I want it to see every single competence that appears for a role and provide an output with this role's frequencies.

Graph 1 (how the data is displayed now)

Graph 2 (how I want it)

[Daviejoe]

How are you wanting to display on the bar chart?  By individual competency?

[airielo]

Say, I want to visualize a frequency of each competency per role. For example, we have a Data scientist and for that role the key competences entered are: Python, 70; R, 60; SQL, 50. If I choose another role, Developer, it will be C#, 70; JavaScript, 60; Python, 50 etc etc.

Since the data for competences is entered as a long comma-separated string (for ex. Data Scientist: "Python, sql, R, tableau, tensorflow), my question is how do I count the individual competences within the role?

Thanks!