DAX to count text from the columns containing comma seperated values

[as5948]

Hi All, I have a column like this which contains 0-4 values from 6 of these values "A,B,C,D,E,F"
Input

Changes

A,B,D,E

A,E

C

C,E,A

D

I want a DAX to return the count of A,B,C,D,E,F
Like
A = 3

B=1

C=2

D=2

E=3

F=0

[tamerj1 · Accepted solution]

Hi @as5948 
Please refer to attached sample file. In case you don't have the table of the unique changes then use

Then use the followig measure

[ThxAlot · Accepted solution]

Easy solution for beginner

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nHRcVWK1QGxIbQzhNRx1XEEs1yUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Changes = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Changes", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Changes"),
    Count = Table.Group(#"Split Column by Delimiter", "Changes", {"Count", Table.RowCount})
in
    Count

[tamerj1 · Accepted solution]

Hi @as5948 
Please refer to attached sample file. In case you don't have the table of the unique changes then use

Then use the followig measure