cancel
Showing results for 
Search instead for 
Did you mean: 
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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

13 REPLIES 13
MFelix
Super User III
Super User III

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Sean
Community Champion
Community Champion

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




For posterity:

 

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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 IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Anonymous
Not applicable

Sure!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors