Cthulhu

Super User
835 Views
Highlighted
Super User
Posts: 9,958
Registered: ‎07-11-2015

Cthulhu

[ Edited ]

Why Cthulhu? Because the mental gymnastics required to figure this out nearly drove me insane. And because the exact reason you would need this measure is probably beyond the comprehension of mere mortals. Besides, what else am I supposed to call it, "Repeating Counter Indexing Thingy"?

 

But, if you need a column or measure that counts a group of things consequetively but restarts after a non-consequetive row, well then you are likely the second person to need this...

 

 

Cthulhu = 
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('Cthulhu'),[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('Cthulhu'),[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.

The other person would be this guy @coreyweiss in this thread: https://community.powerbi.com/t5/Desktop/Consecutive-Row-Counter-Column/td-p/509553/highlight/false

 

 

 

 

 


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

Proud to be a Datanaut!


Attachment
Member
Posts: 52
Registered: ‎06-06-2018

Re: Cthulhu

[ Edited ]

Hahaha, I wish the people using this visual truly know the blood sweat and tears behind it! Thanks for your hard work Greg!

Member
Posts: 52
Registered: ‎06-06-2018

Re: Cthulhu

Note: this is used to splice up a sensor input by shift hour: ie

 

shift 1:

7am hour 1

8am hour 2

9am hour 3

 

Shift 2:

10pm hour 1

11pm hour 2

.....

 

Not for counting zoo animals!