Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Amended the GroupMinTime formula which resolved the issue. Your formulas helped a liot so gving you kudos!
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
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!
Hi @Etienne123,
Great to hear the problem got resolved! Could you accept the corresponding reply as solution to close this thread?
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |