Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Consecutive Row Counter Column

 Hi there!

 

I am looking to make a counter that counts the consecutive rows and resets when there is a nonconsecutive value.

 

Here is an example:

 

 

AnimalCounter
Tiger1
Tiger2
Tiger3
Tiger4
Tiger5
Lion1
Lion2
Lion3
Tiger1
Tiger2
Elephant1
Elephant2
Elephant3
Tiger1
Tiger2
Tiger3

 

Any thoughts?

 

Thanks!

1 ACCEPTED SOLUTION

See if this works:

 

Column 2 = 
VAR __index = CALCULATE(MAX([Index]))
VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index]))
VAR __max = MAXX(__tmpTable2,[Index])
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart)
RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1))

PBIX is attached. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

13 REPLIES 13
MFelix
Super User
Super User

Hi @Anonymous,

 

Based on the solution made by @ImkeF on this post I have made some adjustments and arrived to the requested result on query editor.

 

Basically I add the solution from in the post but added some previous steps :

  • Added an index column
  • Based on the index column I get the previous row animal name
  • Added a column to compare the Animal name in original column and the new one and return on that column the Index number if animal is diffent
  • Fill down the null values in order to get an additional column with the same number for each animal

 

This last column is the base for you keeping the order if you apply the solution on the post above you will get the animals together and number from 1 to 10 on the tiger for example

 

Check the M Code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMTy1S0lEyVIrVQfCMUHjGKDwTFJ4pmOeTmZ8HNwTKMULmoBqBzTLXnNSCjMS8Erg0kgCGCsLGIbk9FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Animal = _t, Counter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Animal", type text}, {"Counter", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Counter"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then [Animal] else #"Added Index"{[Index] - 1}[Animal] ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Index] = 0 then [Index] else if [Animal] = [Custom] then null else [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Index", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Animal", "Custom.1"}, {{"Grouping", each _, type table}}),
    Custom1 = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Grouping], "Index", 1,1)),
    #"Removed Columns2" = Table.RemoveColumns(Custom1,{"Grouping", "Custom.1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Index"}, {"Custom.Index"})
in
    #"Expanded Custom"

If you want I can post a gif image.

 

@ImkeF do you have any suggestion to make this more easier or do you agree with this approach?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks Felix for your reply!

 

This unfortunately must be done with a calculated column since the "Animal" column does not exist in the query. 

Hi @MFelix,

setting the 4th (optional) parameter in the Table.Group-function to "GroupKind.Local" will do some magic in this use case 😉

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMTy1SitUhnuWTmZ+Hg4FNuWtOakFGYl4JAQ4eO2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Animal = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Animal", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Animal"}, {{"Partition", each Table.AddIndexColumn(_, "Counter", 1,1), type table}}, GroupKind.Local),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Counter"}, {"Counter"})
in
    #"Expanded Partition"

@Anonymous: May I ask what your source-data exactly looks like?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF@MFelixThank you so much for replying!!!

 

This dataset is much more hairy than what I posted as an example and cannot begin to share the source data 😕 . I require a DAX solution unfortunately.

See if this works:

 

Column 2 = 
VAR __index = CALCULATE(MAX([Index]))
VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index]))
VAR __max = MAXX(__tmpTable2,[Index])
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart)
RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1))

PBIX is attached. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I just wanted to add this link to this topic!

Related to ImkeF's solution...

https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

 

Anyway I'll "process" Smiley Very Happy @Greg_Deckler's solution later today Smiley Wink

@Sean - Here it is with comments if it helps! 🙂

 

Column 2 = 
VAR __index = CALCULATE(MAX([Index])) //What is my current row index?
VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index])) //Return all rows earlier than the current row within the same "group"
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) //For each returned row, calculate the difference between the index values within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1
VAR __max = MAXX(__tmpTable2,[Index]) //Figure out the max index in the current table.
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again)
VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip
RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Also, because I was bored, here it is as a measure! With a few small improvements and a little bit better commenting.

 

Measure 12 = 
VAR __index = CALCULATE(MAX([Index])) //What is my current row index?
VAR __group = CALCULATE(MAX([Animal])) //What is my current group?
VAR __tmpTable1 = FILTER(ALL('Table34'),[Animal]=__group&&[Index]<__index) //Return all rows earlier than the current row within the same "group"
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) //For each returned row, calculate the difference between the current index value and the previous index value within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1
VAR __max = MAXX(__tmpTable2,[Index]) //Figure out the max index in the current filtered table.
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again) This will be the greatest index where the difference from the previous index in the same group is greater than 1 (previous row)
VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip
RETURN IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

For posterity:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Just curious @Greg_Deckler or anyone else who worked on this....have you tested it on a large data set? I haven't worked much with temp tables inside of a DAX measure and am curious how they perform. Any idea how long it would take for a table or visual to load if you had millions of rows?

 

You can change the Animal by any column you have in your table I picked up your example so I assumed you had that column on your source data.

Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Greg_Deckler
Super User
Super User

Can we assume that you would add an Index column to this in Power BI?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Sure!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.