Cthulhu 09-06-2018 15:27 PM - last edited 04-24-2024 08:03 AM

[Greg_Deckler]

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 consecutively but restarts after a non-consecutive 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 @Anonymous in this thread: https://community.powerbi.com/t5/Desktop/Consecutive-Row-Counter-Column/td-p/509553/highlight/false

eyJrIjoiMTBhYmFlZjMtZDhhMy00MGFjLThkZWQtNDc5MDM2M2ZjN2UzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[AlexisOlson]

I think I found a cleaner approach:

Local Group Index  = 
VAR _CurrIndex = MAX ( Cthulhu[Index] )
VAR _CurrGroup = MAX ( Cthulhu[Animal] )
VAR _LocalGroupStart =
    CALCULATE (
        MAX ( Cthulhu[Index] ),
        ALLSELECTED ( Cthulhu ),
        Cthulhu[Index] < _CurrIndex,
        Cthulhu[Animal] <> _CurrGroup
    )
VAR _LocalIndex = _CurrIndex - _LocalGroupStart
RETURN
    _LocalIndex

[Greg_Deckler]

I'll have to give them a try. The more modern version of Cthulhu can be found inside of Streaks! Streaks! - Microsoft Fabric Community

[AlexisOlson]

I've got a simplification for that problem now too. 🙂

[AlexisOlson]

@Greg_Deckler, here's an approach using the newer WINDOW functions.

Local Group Index  = 
VAR _GlobalGroup_ = /* All rows with current animal up to current row */  
    WINDOW (
        1, ABS, /* From frirst row */
        0, REL, /* Up to current row */
        ORDERBY ( Cthulhu[Index] ),
        PARTITIONBY ( Cthulhu[Animal] )
    )  
VAR _AddPrevIndex_ = /* Lookup previous index for each row */
    ADDCOLUMNS (
        _GlobalGroup_,
        "@PrevIndex",
	        VAR _PrevRow_ =
                OFFSET ( -1, _GlobalGroup_, ORDERBY ( Cthulhu[Index] ) )
	        VAR _PrevIndex =
                MAXX ( _PrevRow_, Cthulhu[Index] )
	        RETURN
                _PrevIndex
    )
VAR _Skips_ = /* All rows where prev index isn't current index - 1 */
    FILTER ( _AddPrevIndex_, [@PrevIndex] < Cthulhu[Index] - 1  )
    /* Will be empty if there are no such skips */

VAR _LocalGroupStart = /* Latest skip -- blank if _Skips_ is empty */
    MAXX ( _Skips_, Cthulhu[Index] )

VAR _LocalGroup_ = /* All rows since latest skip */
    FILTER ( _AddPrevIndex_, Cthulhu[Index] >= _LocalGroupStart )

VAR _Rank = /* Current row is the last in the local group */
    COUNTROWS ( _LocalGroup_ )

RETURN  
    _Rank

[Calimamax2]

I'm so confused.  I'm a not-very-tech-savvy senior so calculations etc throw me for a loop.  I have a data dump from our timekeeping database. Columns include: Employee, Division, Section, PayGrade, Date, Hours, to name a few. There is 11,000 rows with 200 employees and the dates they worked.  I need to add a column to the dataset or a measure to a table visual to show how many consecutive days they worked (including weekend days) so that I can identify those who worked 7 consecutive days or more (and needs to reset after a 1-day gap).  I watched the video and read whatever I could find. And I'm stumped. I tried to copy the Cthulhu calculation and I got an error.  Any help would be appreciated.   (I've attached the calc pic in PBI with the error and the calc in excel so the bottom of the calc shows)

[eugenm]

I may be a 3rd guy... Looks like I may be using this work.

I would have preferred a Power Query based solution, as the mix of PQ and using add column or measure is a bit too unpredictable for a beginner like me. I have lots of PQ in a complex table, along with using this, as an add column.

It worked! But devs are buffled as to how it is all works. 

Amazing work - I've just signed up to say thank you!... 

[Greg_Deckler]

@eugenm - Glad it is of use! It was a fun one to put together. For a Power Query version, your best bet would be @ImkeF !

[ImkeF]

I call that "Nested Index"-column: https://www.youtube.com/watch?v=-3KFZaYImEY

Table.Group(YourTable, {"Animal"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1), type table}})

and expand 😉

[eugenm]

Thank you. I will circle back, should I have any questions.

Your help is much appreciated.

[Anonymous]

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

[Anonymous]

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!