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