Count Rows with condition to get sequential number for multiple entries

[ReMo]

Hi everyone,

I am facing a challenge in Power BI desktop where I could use your expertise.

My data is huge and I am looking for a formula to help me counting the number of rows based on a certain condition.

Taking the below table example, I have 12 rows with a notifiaction number in column A.

The notifiaction number can appear 1-x times.

What I am looking for is a formula that returns a 1 for every first line the notifiaction number appears, a 2 for the 2nd, a 3 for the 3rd and so on and so forth and then starts with 1 again as soon as the notifiaction number is different.

In Excel I'd start with entering a 1 into my column called "count" and the first row for notifiaction A and then the following formula into further columns:

=IF(A3=A2;B2+1;1)

notification number	count
A	1
A	2
A	3
B	1
C	1
C	2
D	1
E	1
E	2
E	3
E	4
F	1

It would be great when anyone in this forum could help me to get to the result I need.

Thank you

[Greg_Deckler]

@ReMo That is Cthulhu. Cthulhu - Microsoft Power BI Community

[ReMo]

@Greg_Deckler I don't mind to use DAX or Power Query, I just don't know how to get to the result I need.

[Greg_Deckler]

@ReMo Cthulhu is how you get the result you need. First, you need to add an Index column in Power Query. PBIX is attached below signature. This is as a measure:

Cthulhu = 
    VAR __index = MAX([Index])
    VAR __group = MAX([notification number])
    VAR __tmpTable1 = FILTER(ALL('Table'),[notification number]=__group&&[Index]<__index) 
    VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table'),[Index]<EARLIER([Index]) && [notification number]=EARLIER([notification number])),[Index])) 
    VAR __max = MAXX(__tmpTable2,[Index]) 
    VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
    VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart)
    VAR __Result = IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1)) 
RETURN
    __Result

This is the column version:

Cthulhu Column = 
    VAR __index = [Index]
    VAR __group = [notification number]
    VAR __tmpTable1 = FILTER(ALL('Table'),[notification number]=__group&&[Index]<__index) 
    VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table'),[Index]<EARLIER([Index]) && [notification number]=EARLIER([notification number])),[Index])) 
    VAR __max = MAXX(__tmpTable2,[Index]) 
    VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
    VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart)
    VAR __Result = IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1)) 
RETURN
    __Result