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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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!