cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saxon10
Post Prodigy
Post Prodigy

Count occurrence

How can I do runining count/ Count occurrence in Power BI based on the item. I search in Power BI forum and I can't found anything similerly . Can you please advise.

 

Can you please advise both solution New calculated column and Measure

 

 

ITEM Desired Result (COUNT)
1231
1232
1233
1234
1235
1236
1237
1238
1239
12310
12311
12312
12313
12314
12315
12316
12351
12352
12353
67891
43421
43422
43423
43424

 

Saxon10_0-1618046546125.png

 

1 ACCEPTED SOLUTION
Karlos
Super User I
Super User I

I'm not sure how you'd do it using Dax but this is how I'd do it in Power Query 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVGVDaFAfDzNzCEswwMTYxwsuIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Input"}, {{"Desired Result (COUNT)", each _, type table [Input=text]}}),
    #"Add Index to tbl" = Table.TransformColumns( #"Grouped Rows", {{ "Desired Result (COUNT)", each Table.AddIndexColumn( _, "Index", 1, 1 ) , type table [Input=text, Index=number] }} ),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Add Index to tbl", "Desired Result (COUNT)", {"Index"}, {"Desired Result (COUNT)"})
in
    #"Expanded tbl"

 

Karlos_0-1618048255097.png

 

To solve it in Dax, you'd need an index column adding also -
Solved: [DAX] Running count of occurrence by category, res... - Microsoft Power BI Community

View solution in original post

4 REPLIES 4
Karlos
Super User I
Super User I

I'm not sure how you'd do it using Dax but this is how I'd do it in Power Query 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVGVDaFAfDzNzCEswwMTYxwsuIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Input"}, {{"Desired Result (COUNT)", each _, type table [Input=text]}}),
    #"Add Index to tbl" = Table.TransformColumns( #"Grouped Rows", {{ "Desired Result (COUNT)", each Table.AddIndexColumn( _, "Index", 1, 1 ) , type table [Input=text, Index=number] }} ),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Add Index to tbl", "Desired Result (COUNT)", {"Index"}, {"Desired Result (COUNT)"})
in
    #"Expanded tbl"

 

Karlos_0-1618048255097.png

 

To solve it in Dax, you'd need an index column adding also -
Solved: [DAX] Running count of occurrence by category, res... - Microsoft Power BI Community

View solution in original post

Hi,

 

Thanks for your reply and solution and relevant article.  The item column came from another dax so I can't use it the query option because the item column won't be there in power query.

 

Can you please advise how can I generate index column by using DAX _New calculate column so I will try tp apply solution based on the link you provided. 

 

 

If you're trying to generate it in Dax, it depends on what other columns you have that you can reference because the Item column alone won't be enough. If you can use power query, it's a click of a button. 

Thanks for your reply and advice. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors