Forum Discussion
DannyDiaz
1 year agoNew Member
Need help - Get a table to identify consecutive values between groups
Hello, I have a table listing 3 types of products and hundreds of employees SHOP EMPLOYEE PRODUCT TIMELINE - DAY A Tom Car 1 20 A Tom Car 1 21 ...
- 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!