Unique id for a group of records

[Etienne123]

Hi

I have a recurring group value (Sector). I need to create a unique key for ech occurrence of the group. Any idea how I can achieve the GroupKey shown below? Note how A occvurs twice, but it must be given a different GroupKey.

DateTime : Sector : GroupKey
20/09/2017 13:10:48 : A : 1
20/09/2017 13:38:17 : A : 1
20/09/2017 13:52:16 : A : 1
20/09/2017 13:53:10 : B : 2
20/09/2017 13:54:58 : B : 2
20/09/2017 13:55:26 : A : 3
20/09/2017 13:58:09 : A : 3

[Phil_Seamark]

HI @Etienne123

Here is one approach that requires adding some calculated columns to your table

Index = CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),'Table3'[DateTime] < EARLIER('Table3'[DateTime])))+1

This column adds an index to your table based on DateTime

Column = 
VAR S1 = 'Table3'[Sector]
VAR S2 = CALCULATE(MIN('Table3'[Sector]),FILTER('Table3','Table3'[Index] = EARLIER('Table3'[Index])-1))
VAR S3 = if(S1<>S2,[DateTime],BLANK())
RETURN S3

This works out the starting row in each series and marks the datetime

Column 2 = CALCULATE(MAX('Table3'[Column]),FILTER(all('Table3'),'Table3'[Index] <= EARLIER('Table3'[Index])))

Column 2 fills the datetime down

Finally

GroupKey = CALCULATE(
                DISTINCTCOUNT(
                    'Table3'[Column 2]),
                    ALL('Table3'),
                    'Table3'[Column 2] < EARLIER('Table3'[Column 2])
                    )+1

[Etienne123]

Hi,

Your formulas helped, but does not work entirely. There is a larger post re this issue here - https://community.powerbi.com/t5/Desktop/Cumulative-Sum-reset-by-recurring-group/m-p/263153/highligh...

[Etienne123 · Accepted solution]

Amended the GroupMinTime formula which resolved the issue. Your formulas helped a liot so gving you kudos!

[v-ljerr-msft]

Hi @Etienne123,

Great to hear the problem got resolved! Could you accept the corresponding reply as solution to close this thread? 

Regards