cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Etienne123 Frequent Visitor
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
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
Phil_Seamark Super Contributor
Super Contributor

Re: Unique id for a group of records

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Etienne123 Frequent Visitor
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
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
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? Smiley Happy

 

Regards