cancel
Showing results for
Did you mean:
Highlighted
Etienne123 Frequent Visitor

## Unique id for a group of records

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

1 ACCEPTED SOLUTION

Accepted Solutions
Etienne123 Frequent Visitor

## Re: Unique id for a group of records

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

4 REPLIES 4 Super User

## Re: Unique id for a group of records

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```

Proud to be a Datanaut!

Etienne123 Frequent Visitor

## Re: Unique id for a group of records

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 Frequent Visitor

## Re: Unique id for a group of records

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

v-ljerr-msft Super Contributor

## Re: Unique id for a group of records

Hi @Etienne123,

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