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
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 - RankDay
This 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!