Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
AlexisOlson
Super User
4 years ago

I'm not sure what you mean by "skipping". What result are you expecting instead?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I am looking for the data to read like the table below. That way I can see the Max(How Many Times) which will tell me how many times a project went in and out of the Tech Review phase.

 

Project IDStage IdCreatedStage_NameIndexHow many times "Tech Review"
69011394/21/2022Prod Files1 
69010314/20/2022Tech Review25
69010864/19/2022Revised Proof3 
69011404/14/2022Revised Proof4 
69010314/14/2022Tech Review54
69010862/22/2022Revised Proof6 
69010312/20/2022Tech Review73
69010222/17/2022Graphics8 
69011352/17/2022Estimating9 
69010311/28/2022Tech Review102
69010311/28/2022Tech Review112
69010231/28/20221st proof12 
69010471/27/2022In Production13 
69010221/27/2022Graphics14 
69010311/27/2022Tech Review151
114310864/21/2022Revised Proof1 
114310244/21/2022Revised Proof2 
114311354/19/2022Estimating3 
114310864/18/2022Revised Proof4 
114310244/18/2022Revised Proof5 
114310224/18/2022Graphics6 
114310314/18/2022Tech Review79
114311404/12/2022Revised Proof8 
114310314/12/2022Tech Review98
114310864/11/2022Revised Proof10 
114310244/11/2022Revised Proof11 
114310314/8/2022Tech Review127
114310224/7/2022Graphics13 
114310313/29/2022Tech Review146
114310863/29/2022Revised Proof15 
114310243/29/2022Revised Proof16 
114310863/28/2022Revised Proof17 
114310313/23/2022Tech Review185
114310223/22/2022Graphics19 
114310863/15/2022Revised Proof20 
114310233/15/20221st proof21 
114310313/7/2022Tech Review224
114310223/4/2022Graphics23 
114310863/3/2022Revised Proof24 
114310243/3/2022Revised Proof25 
114310863/2/2022Revised Proof26 
114310313/2/2022Tech Review273
114310312/28/2022Tech Review283
114310212/24/2022Graphics29 
114310312/2/2022Tech Review302
114310312/2/2022Tech Review312
114310312/2/2022Tech Review322
114311242/1/2022Estimating33 
114310311/28/2022Tech Review341
114310311/21/2022Tech Review351
114311361/20/2022Sent To Client36 
114311241/20/2022Estimating37 
41610314/21/2022Tech Review18
41610224/20/2022Graphics2 
41610863/29/2022Revised Proof3 
41610223/29/2022Graphics4 
41610313/28/2022Tech Review57
41610223/25/2022Graphics6 
41610313/21/2022Tech Review76
41610863/21/2022Revised Proof8 
41610313/21/2022Tech Review95
41610863/21/2022Revised Proof10 
41610243/21/2022Revised Proof11 
41610213/18/2022Graphics12 
41610213/17/2022Graphics13 
41610213/15/2022Graphics14 
41611353/11/2022Estimating15 
41610863/9/2022Revised Proof16 
41610313/3/2022Tech Review174
41610243/1/2022Revised Proof18 
41611353/1/2022Estimating19 
41610312/4/2022Tech Review203
41610312/3/2022Tech Review213
41610312/3/2022Tech Review223
41610312/2/2022Tech Review233
41610312/1/2022Tech Review243
41610312/1/2022Tech Review253
41610312/1/2022Tech Review263
41610311/28/2022Tech Review273
41610311/25/2022Tech Review283
41610311/18/2022Tech Review293
41610311/17/2022Tech Review303
416103112/17/2021Tech Review313
416103112/8/2021Tech Review323
416102212/7/2021Graphics33 
416103112/3/2021Tech Review342
416106112/3/2021Graphics35 
416103112/2/2021Tech Review361
416103111/23/2021Tech Review371
416113311/22/2021Follow up38 
416113811/22/2021Scheduling39 
416111311/18/2021Clarification40 
416102111/17/2021Graphics41 
  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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"
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It Works! thank you so much

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 IDStage IdCreatedStage_NameIndexHow many times "Tech Review"Assigned "Tech Review"Left "Tech Review"
      69011394/21/2022Prod Files1  1
      69010314/20/2022Tech Review251 
      69010864/19/2022Revised Proof3   
      69011404/14/2022Revised Proof4  1
      69010314/14/2022Tech Review541 
      69010862/22/2022Revised Proof6  1
      69010312/20/2022Tech Review731 
      69010222/17/2022Graphics8   
      69011352/17/2022Estimating9   
      69010311/28/2022Tech Review102  
      69010311/28/2022Tech Review1121 
      69010231/28/20221st proof12   
      69010471/27/2022In Production13   
      69010221/27/2022Graphics14  1
      69010311/27/2022Tech Review1511 
      114310864/21/2022Revised Proof1   
      114310244/21/2022Revised Proof2   
      114311354/19/2022Estimating3   
      114310864/18/2022Revised Proof4   
      114310244/18/2022Revised Proof5   
      114310224/18/2022Graphics6  1
      114310314/18/2022Tech Review791 
      114311404/12/2022Revised Proof8  1
      114310314/12/2022Tech Review981 
      114310864/11/2022Revised Proof10   
      114310244/11/2022Revised Proof11  1
      114310314/8/2022Tech Review1271 
      114310224/7/2022Graphics13  1
      114310313/29/2022Tech Review1461 
      114310863/29/2022Revised Proof15   
      114310243/29/2022Revised Proof16   
      114310863/28/2022Revised Proof17  1
      114310313/23/2022Tech Review1851 
      114310223/22/2022Graphics19   
      114310863/15/2022Revised Proof20   
      114310233/15/20221st proof21  1
      114310313/7/2022Tech Review2241 
      114310223/4/2022Graphics23   
      114310863/3/2022Revised Proof24   
      114310243/3/2022Revised Proof25   
      114310863/2/2022Revised Proof26  1
      114310313/2/2022Tech Review273  
      114310312/28/2022Tech Review2831 
      114310212/24/2022Graphics29  1
      114310312/2/2022Tech Review302  
      114310312/2/2022Tech Review312  
      114310312/2/2022Tech Review3221 
      114311242/1/2022Estimating33  1
      114310311/28/2022Tech Review341  
      114310311/21/2022Tech Review3511 
      114311361/20/2022Sent To Client36   
      114311241/20/2022Estimating37   
      41610314/21/2022Tech Review181 
      41610224/20/2022Graphics2   
      41610863/29/2022Revised Proof3   
      41610223/29/2022Graphics4  1
      41610313/28/2022Tech Review571 
      41610223/25/2022Graphics6  1
      41610313/21/2022Tech Review761 
      41610863/21/2022Revised Proof8  1
      41610313/21/2022Tech Review951 
      41610863/21/2022Revised Proof10   
      41610243/21/2022Revised Proof11   
      41610213/18/2022Graphics12   
      41610213/17/2022Graphics13   
      41610213/15/2022Graphics14   
      41611353/11/2022Estimating15   
      41610863/9/2022Revised Proof16  1
      41610313/3/2022Tech Review1741 
      41610243/1/2022Revised Proof18   
      41611353/1/2022Estimating19  1
      41610312/4/2022Tech Review203  
      41610312/3/2022Tech Review213  
      41610312/3/2022Tech Review223  
      41610312/2/2022Tech Review233  
      41610312/1/2022Tech Review243  
      41610312/1/2022Tech Review253  
      41610312/1/2022Tech Review263  
      41610311/28/2022Tech Review273  
      41610311/25/2022Tech Review283  
      41610311/18/2022Tech Review293  
      41610311/17/2022Tech Review303  
      416103112/17/2021Tech Review313  
      416103112/8/2021Tech Review3231 
      416102212/7/2021Graphics33  1
      416103112/3/2021Tech Review3421 
      416106112/3/2021Graphics35  1
      416103112/2/2021Tech Review361  
      416103111/23/2021Tech Review3711 
      416113311/22/2021Follow up38   
      416113811/22/2021Scheduling39   
      416111311/18/2021Clarification40   
      416102111/17/2021Graphics41   

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        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.