Occurrence of value (or) count value(text) in a column

[Anonymous]

Hi,

I'm trying to calculate the occurence of products in a column .Below is the table:

Products

a,b

b

c,a

d

Expected Result:

Products	Count_prod
a	2
b	2
c	1
d	1

Can we achieve this in DAX (or) power query? Thanks in advance for the help!!

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can use the following formula to create a new calculated table based on the original table products field:

NEW Table =
VAR _path =
    SUBSTITUTE (
        CONCATENATEX ( ALL ( 'Table'[Products] ), [Products], "," ),
        ",",
        "|"
    )
VAR _length =
    PATHLENGTH ( _path )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Product", PATHITEM ( _path, [Value] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _pathtable, [Product] ),
        "Count", COUNTROWS ( FILTER ( _pathtable, [Product] = EARLIER ( [Product] ) ) )
    )

Reference link:

DAX how split a string by delimiter into a list or array? 

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msftThanks this works. To add up further, If there's a scenario where we have multiple columns and want to rollup subtotals like one below can we use the same formula? :

Sales Table:

Name	Products
Tom	a,b
Harry	b
Tom	c, a
Potter	d

Expected Result:

Products	Count_prod
A	2
Tom	1
Tom	1
B	2
Harry	1
Tom	1
C	1
Tom	1
D	1
Potter	1

[Greg_Deckler]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLilWitWJVkrUSQLTEDJZJxFMpyjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Products", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Products", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Products.1", "Products.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products.1", type text}, {"Products.2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

[parry2k]

@Anonymous or you can try this, if you don't want to group in power query, remove last step and then with DAX you can group  by product

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLilWitWJVkrUSQLTEDJZJxFMpyjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Products", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Products", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Products"),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Products"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"