Forum Discussion
Count Column by Group and filter Part 2.
I am posting a new thread because when I reply with a question about a possible solution, it is removed for Spam. AlexisOlson I need to know how many times a project goes in and out the " Tech ...
Ah, I understand what you mean now.
This requires rather different logic we need to incorporate the concept of adjacent rows using the index.
Try this:
New Many = VAR _CurrPID = 'Table'[Project ID] VAR _CurrIndex = 'Table'[Index] VAR _CurrName = 'Table'[Stage_Name] VAR _SubTable_ = FILTER ( 'Table', 'Table'[Project ID] = _CurrPID && 'Table'[Index] >= _CurrIndex && 'Table'[Stage_Name] = "Tech Review" ) RETURN COUNTROWS ( FILTER ( _SubTable_, VAR _NextName = LOOKUPVALUE ( 'Table'[Stage_Name], 'Table'[Project ID], _CurrPID, 'Table'[Index], 'Table'[Index] + 1 ) RETURN _CurrName = "Tech Review" && _NextName <> "Tech Review" ) )
AlexisOlson Is there a way where you can add addional columns to count When a project enters "Tech Review" and when it leaves "Tech Review"? Like the table below
| Project ID | Stage Id | Created | Stage_Name | Index | How many times "Tech Review" | Assigned "Tech Review" | Left "Tech Review" |
| 690 | 1139 | 4/21/2022 | Prod Files | 1 | 1 | ||
| 690 | 1031 | 4/20/2022 | Tech Review | 2 | 5 | 1 | |
| 690 | 1086 | 4/19/2022 | Revised Proof | 3 | |||
| 690 | 1140 | 4/14/2022 | Revised Proof | 4 | 1 | ||
| 690 | 1031 | 4/14/2022 | Tech Review | 5 | 4 | 1 | |
| 690 | 1086 | 2/22/2022 | Revised Proof | 6 | 1 | ||
| 690 | 1031 | 2/20/2022 | Tech Review | 7 | 3 | 1 | |
| 690 | 1022 | 2/17/2022 | Graphics | 8 | |||
| 690 | 1135 | 2/17/2022 | Estimating | 9 | |||
| 690 | 1031 | 1/28/2022 | Tech Review | 10 | 2 | ||
| 690 | 1031 | 1/28/2022 | Tech Review | 11 | 2 | 1 | |
| 690 | 1023 | 1/28/2022 | 1st proof | 12 | |||
| 690 | 1047 | 1/27/2022 | In Production | 13 | |||
| 690 | 1022 | 1/27/2022 | Graphics | 14 | 1 | ||
| 690 | 1031 | 1/27/2022 | Tech Review | 15 | 1 | 1 | |
| 1143 | 1086 | 4/21/2022 | Revised Proof | 1 | |||
| 1143 | 1024 | 4/21/2022 | Revised Proof | 2 | |||
| 1143 | 1135 | 4/19/2022 | Estimating | 3 | |||
| 1143 | 1086 | 4/18/2022 | Revised Proof | 4 | |||
| 1143 | 1024 | 4/18/2022 | Revised Proof | 5 | |||
| 1143 | 1022 | 4/18/2022 | Graphics | 6 | 1 | ||
| 1143 | 1031 | 4/18/2022 | Tech Review | 7 | 9 | 1 | |
| 1143 | 1140 | 4/12/2022 | Revised Proof | 8 | 1 | ||
| 1143 | 1031 | 4/12/2022 | Tech Review | 9 | 8 | 1 | |
| 1143 | 1086 | 4/11/2022 | Revised Proof | 10 | |||
| 1143 | 1024 | 4/11/2022 | Revised Proof | 11 | 1 | ||
| 1143 | 1031 | 4/8/2022 | Tech Review | 12 | 7 | 1 | |
| 1143 | 1022 | 4/7/2022 | Graphics | 13 | 1 | ||
| 1143 | 1031 | 3/29/2022 | Tech Review | 14 | 6 | 1 | |
| 1143 | 1086 | 3/29/2022 | Revised Proof | 15 | |||
| 1143 | 1024 | 3/29/2022 | Revised Proof | 16 | |||
| 1143 | 1086 | 3/28/2022 | Revised Proof | 17 | 1 | ||
| 1143 | 1031 | 3/23/2022 | Tech Review | 18 | 5 | 1 | |
| 1143 | 1022 | 3/22/2022 | Graphics | 19 | |||
| 1143 | 1086 | 3/15/2022 | Revised Proof | 20 | |||
| 1143 | 1023 | 3/15/2022 | 1st proof | 21 | 1 | ||
| 1143 | 1031 | 3/7/2022 | Tech Review | 22 | 4 | 1 | |
| 1143 | 1022 | 3/4/2022 | Graphics | 23 | |||
| 1143 | 1086 | 3/3/2022 | Revised Proof | 24 | |||
| 1143 | 1024 | 3/3/2022 | Revised Proof | 25 | |||
| 1143 | 1086 | 3/2/2022 | Revised Proof | 26 | 1 | ||
| 1143 | 1031 | 3/2/2022 | Tech Review | 27 | 3 | ||
| 1143 | 1031 | 2/28/2022 | Tech Review | 28 | 3 | 1 | |
| 1143 | 1021 | 2/24/2022 | Graphics | 29 | 1 | ||
| 1143 | 1031 | 2/2/2022 | Tech Review | 30 | 2 | ||
| 1143 | 1031 | 2/2/2022 | Tech Review | 31 | 2 | ||
| 1143 | 1031 | 2/2/2022 | Tech Review | 32 | 2 | 1 | |
| 1143 | 1124 | 2/1/2022 | Estimating | 33 | 1 | ||
| 1143 | 1031 | 1/28/2022 | Tech Review | 34 | 1 | ||
| 1143 | 1031 | 1/21/2022 | Tech Review | 35 | 1 | 1 | |
| 1143 | 1136 | 1/20/2022 | Sent To Client | 36 | |||
| 1143 | 1124 | 1/20/2022 | Estimating | 37 | |||
| 416 | 1031 | 4/21/2022 | Tech Review | 1 | 8 | 1 | |
| 416 | 1022 | 4/20/2022 | Graphics | 2 | |||
| 416 | 1086 | 3/29/2022 | Revised Proof | 3 | |||
| 416 | 1022 | 3/29/2022 | Graphics | 4 | 1 | ||
| 416 | 1031 | 3/28/2022 | Tech Review | 5 | 7 | 1 | |
| 416 | 1022 | 3/25/2022 | Graphics | 6 | 1 | ||
| 416 | 1031 | 3/21/2022 | Tech Review | 7 | 6 | 1 | |
| 416 | 1086 | 3/21/2022 | Revised Proof | 8 | 1 | ||
| 416 | 1031 | 3/21/2022 | Tech Review | 9 | 5 | 1 | |
| 416 | 1086 | 3/21/2022 | Revised Proof | 10 | |||
| 416 | 1024 | 3/21/2022 | Revised Proof | 11 | |||
| 416 | 1021 | 3/18/2022 | Graphics | 12 | |||
| 416 | 1021 | 3/17/2022 | Graphics | 13 | |||
| 416 | 1021 | 3/15/2022 | Graphics | 14 | |||
| 416 | 1135 | 3/11/2022 | Estimating | 15 | |||
| 416 | 1086 | 3/9/2022 | Revised Proof | 16 | 1 | ||
| 416 | 1031 | 3/3/2022 | Tech Review | 17 | 4 | 1 | |
| 416 | 1024 | 3/1/2022 | Revised Proof | 18 | |||
| 416 | 1135 | 3/1/2022 | Estimating | 19 | 1 | ||
| 416 | 1031 | 2/4/2022 | Tech Review | 20 | 3 | ||
| 416 | 1031 | 2/3/2022 | Tech Review | 21 | 3 | ||
| 416 | 1031 | 2/3/2022 | Tech Review | 22 | 3 | ||
| 416 | 1031 | 2/2/2022 | Tech Review | 23 | 3 | ||
| 416 | 1031 | 2/1/2022 | Tech Review | 24 | 3 | ||
| 416 | 1031 | 2/1/2022 | Tech Review | 25 | 3 | ||
| 416 | 1031 | 2/1/2022 | Tech Review | 26 | 3 | ||
| 416 | 1031 | 1/28/2022 | Tech Review | 27 | 3 | ||
| 416 | 1031 | 1/25/2022 | Tech Review | 28 | 3 | ||
| 416 | 1031 | 1/18/2022 | Tech Review | 29 | 3 | ||
| 416 | 1031 | 1/17/2022 | Tech Review | 30 | 3 | ||
| 416 | 1031 | 12/17/2021 | Tech Review | 31 | 3 | ||
| 416 | 1031 | 12/8/2021 | Tech Review | 32 | 3 | 1 | |
| 416 | 1022 | 12/7/2021 | Graphics | 33 | 1 | ||
| 416 | 1031 | 12/3/2021 | Tech Review | 34 | 2 | 1 | |
| 416 | 1061 | 12/3/2021 | Graphics | 35 | 1 | ||
| 416 | 1031 | 12/2/2021 | Tech Review | 36 | 1 | ||
| 416 | 1031 | 11/23/2021 | Tech Review | 37 | 1 | 1 | |
| 416 | 1133 | 11/22/2021 | Follow up | 38 | |||
| 416 | 1138 | 11/22/2021 | Scheduling | 39 | |||
| 416 | 1113 | 11/18/2021 | Clarification | 40 | |||
| 416 | 1021 | 11/17/2021 | Graphics | 41 |
Yes. The logic is very similar.
Assigned Tech Review =
VAR _CurrName = 'Table1'[Stage_Name]
VAR _NextName =
LOOKUPVALUE (
'Table1'[Stage_Name],
'Table1'[Project ID], 'Table1'[Project ID],
'Table1'[Index], 'Table1'[Index] + 1
)
RETURN
IF ( _NextName <> "Tech Review" && _CurrName = "Tech Review", 1 )
For Left Tech Review, do the same except swap the "<>" and "=" in the last line.