Forum Discussion
Need help - Get a table to identify consecutive values between groups
- 1 year ago
Hi DannyDiaz
Add a calculated column to group streaks
StreakGroup = VAR CurrentDay = 'ConsecutiveDays'[TIMELINE - DAY] VAR RankDay = RANKX ( FILTER ( 'ConsecutiveDays', 'ConsecutiveDays'[EMPLOYEE] = EARLIER ( 'ConsecutiveDays'[EMPLOYEE] ) && 'ConsecutiveDays'[PRODUCT] = EARLIER ( 'ConsecutiveDays'[PRODUCT] ) ), 'ConsecutiveDays'[TIMELINE - DAY], , ASC ) RETURN CurrentDay - RankDayThis gives the following result, just adding column for visibility
Now add a table that will summarize the streaks
StreakSummary = SUMMARIZE ( 'ConsecutiveDays', 'ConsecutiveDays'[EMPLOYEE], 'ConsecutiveDays'[PRODUCT], 'ConsecutiveDays'[StreakGroup], "ConsecutiveDays", COUNTROWS ( 'ConsecutiveDays' ) )Next add a table that will filter the streaks as required
QualifiedStreaks = FILTER ( StreakSummary, [ConsecutiveDays] >= 7 )Finally, add a table to show the result
FinalResult = SELECTCOLUMNS ( QualifiedStreaks, "EMPLOYEE", [EMPLOYEE], "PRODUCT", [PRODUCT], "CONSECUTIVE DAYS", [ConsecutiveDays] )Add this to a table visual for the following result:
I hope this helps, please give a thumbs up and mark as solved if it does, thanks!
Hi DannyDiaz
Thank you for posting your question on the Microsoft Fabric Community Forum, and thanks as well Greg_Deckler , alish_b and wardy912 for the ongoing support and assistance.
Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.
Thank you.