cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Etienne123
Advocate II
Advocate II

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

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

View solution in original post

4 REPLIES 4
Phil_Seamark
Microsoft
Microsoft

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!

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

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

View solution in original post

Hi @Etienne123,

 

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

 

Regards

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!