Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Animal | Counter |
Tiger | 1 |
Tiger | 2 |
Tiger | 3 |
Tiger | 4 |
Tiger | 5 |
Lion | 1 |
Lion | 2 |
Lion | 3 |
Tiger | 1 |
Tiger | 2 |
Elephant | 1 |
Elephant | 2 |
Elephant | 3 |
Tiger | 1 |
Tiger | 2 |
Tiger | 3 |
Any thoughts?
Thanks!
Solved! Go to 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.
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 :
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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
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.
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" @Greg_Deckler's solution later today
@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.
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.
For posterity:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739
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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCan we assume that you would add an Index column to this in Power BI?
Sure!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |