Count the frequency of a specific value in a column with multiple comma separated fixed values?

[ChrisBernatek]

Hello, am new to Power BI so trying to get to grips with some calculated measures.

I have a column in a table that contains multiple comma separted 'topic' values.

I need a formula that will count the number of individual occurances of each unique topic so that I can create a simple chart to show what topics are featuring the most.

See image below for example of the data that is saved in each row - there is typically more than 1 topic in any one cell and the topics are listed in different orders, there are also a lot of blanks. I need a way of just being able to count the number of occurances of each of the topics please.

In excel I would just list out the individual topics in a table (there are only 16 unique values) and then use a "COUNTIFS" formula to count how many times each value appears in the range.

Need to know how to do this in Power BI.

Many thanks

[PattemManohar]

@ChrisBernatek Could you please post the sample data (able to copy and paste) which will be helpful to replicate and resolve the issue quicker.

[ChrisBernatek]

There are 20,000 rows total, but here is a sample of what each cell typically contains in the topic column:

Web search
Sales & Service,Web search,Web navigation,Web product content
Web navigation
Web search
Web search,CAD
Pricing & Delivery
Sales & Service,Pricing & Delivery,Web product content
Product selection
Pricing & Delivery
Web navigation
Pricing & Delivery
Web navigation,Product selection,Configurators
Web navigation
Web search,Web navigation
Web product content
Unknown
Pricing & Delivery
Pricing & Delivery,Web search,Web navigation,Web product content
Pricing & Delivery,Compliment,Web product content,Product selection
Web search
Web navigation
Web search
Sales & Service,Web search,Web navigation,Distributors
Web search,Web navigation,Web product content
Web navigation
Pricing & Delivery,Distributors
Product selection
Unknown
CAD
Sales & Service,Pricing & Delivery,Web navigation
Web search,Web navigation
Unknown
Unknown
Web navigation,Web product content
Unknown
Web navigation
Sales & Service,Pricing & Delivery
CAD
Web search,CAD,Configurators
Web navigation
Web search

The unique topic values are as follows:

Web search

Web navigation

Web product content

Product selection

Sales & Service

CAD

Pricing & Delivery

Compliment

Configurators

Distributors

No suggestion

Career / HR / Corp

Wants to be contacted

Product quality

No response provided

Many thanks

[PattemManohar · Accepted solution]

@ChrisBernatek Thanks for providing the sample data.

Here is the solution, that is achieved in "Power Query". You can use "Split-Column" option and make sure you split them into rows as below:

Then use "Group-By" option to just group the topic and count, the output will be as follows:

For your reference, here is the steps I've followed. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVTLDoIwEPwVwrkH4sG7gQ8gIcYDcih1xY3Qmj4w/r2GCJZAgcZbu93dmZ1Om+fhCcpAAZXsFhYkDzNagwrOJop2+yAD2SID8svplpy2WFGNgnfbhxQXw3TABNfAdddmnDaELCCrZ3xIulAqkSGvevQEamxBvty8HAVOVuk3pqAGNhBbgp2ZY3s6meCRWPArVkZSLaRaV4o4TueGO/I7F891jkui+V2yq1MsmkeNzSdtrnSqisseywbydGqCSksszUj4v03tkmCCNj+0fWf9K/B2upd7bER7vVECd4k399HQ49/A95mERfEG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TextData = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextData", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"TextData", "Topic"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Topic", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Topic"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Topic", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Topic"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

[ChrisBernatek]

Thanks for the solution but I am looking to achieve the same result but by using a Measure. 

Is this possible?

For example - I would want a DAX formula to be able to count the number of times the word "CAD" appears in the topic column. 

I have a simple Measure that can count the number of times the word "CAD"  appears, but it only counts it when that is the only word in that column - it doesn't count up the times where "CAD" appears along with other topics in the cell. Ie where the data looks like this: "CAD,Web Search,Complimnent"..

Is there a DAX formula that can just count up the number of times a word appears, regardless of the cells having other comma separated words in it?

Many thanks