How to count number of occurences of a value

[Anonymous]

I have a column like this. 

A

B

C

A

B

C

A

I want a second column with a count of how many of the values have been seen so far, just like this.

A 1

B 1

C 1

A 2

B 2

C 2

A 3

[Samarth_18 · Accepted solution]

HI @Anonymous ,

Create a index column in your table then create a column with below code:-

Occurrences = 
CALCULATE (
    COUNT ([Column1] ),
    FILTER (
        ALL('Table (4)'),
        [Index] <= EARLIER ( [Index] )
            && [Column1] = EARLIER ( [Column1] )
    )
)

Output:-

Thanks,

Samarth

[Anonymous]

Thanks @Samarth_18 , it worked

[parry2k]

@Anonymous change the column name in the script to the column name from your table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmCSSJFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TagName", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TagName"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}),
    #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Rank"}, {"Rank.1"})
in
    #"Expanded Rank"

basically steps from #"Grouped Rows" is what you needed.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi @parry2k ,

Thanks, I tried this but I got an error saying the column "TagName" in the table wasn't found. Do I have to specify my table name somewhere? It is called Panel.

[mh2587]

Referring to this link hope it will help you

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-How-to-Count-the-Consecutive-Occurrences/...

[parry2k]

@Anonymous you should add a index column in Power Query, start a blank query in PQ, click advanced editor and paste this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmCSSJFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}),
    #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Rank"}, {"Rank.1"})
in
    #"Expanded Rank"

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi @parry2k , thanks for your reply. The letters I used were only an example. The column is more like shown below. Do you know how I can modify the code to count all the values in this column? 

Note: This is just a snippet, there is around 40 unique tags that get repeated.