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 Review" phase. The possible soultion was provided in a previous thread which i have posted below. How many times = VAR _maxIndexOf_ProjectAndStage = MAXX ( FILTER ( 'Table', 'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] ) && 'Table'[Stage_Name] = "Tech Review" ), 'Table'[ProjectIndex] ) VAR _maxIndexOfProject = MAXX ( FILTER ( 'Table', 'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] ) ), 'Table'[ProjectIndex] ) VAR _countNotTechReview = COUNTROWS ( FILTER ( 'Table', 'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] ) && 'Table'[ProjectIndex] > EARLIER ( 'Table'[ProjectIndex] ) && 'Table'[Stage_Name] <> "Tech Review" ) ) RETURN IF ( 'Table'[Stage_Name] = "Tech Review", _countNotTechReview - ( _maxIndexOfProject - _maxIndexOf_ProjectAndStage - 1 ) Below is the table results from the logic above. As you can see, it skips values in the "how many times" column. I think this is really close but I cannot figure out the last step. Thank you! Project IDStage IdCreatedStage_NameIndexHow many times "Tech Review"69011394/21/22Prod Files1 69010314/20/22Tech Review2969010864/19/22Revised Proof3 69011404/14/22Revised Proof4 69010314/14/22Tech Review5769010862/22/22Revised Proof6 69010312/20/22Tech Review7669010222/17/22Graphics8 69011352/17/22Estimating9 69010311/28/22Tech Review10469010311/28/22Tech Review11469010231/28/221st proof12 69010471/27/22In Production13 69010221/27/22Graphics14 69010311/27/22Tech Review151114310864/21/22Revised Proof1 114310244/21/22Revised Proof2 114311354/19/22Estimating3 114310864/18/22Revised Proof4 114310244/18/22Revised Proof5 114310224/18/22Graphics6 114310314/18/22Tech Review717114311404/12/22Revised Proof8 114310314/12/22Tech Review916114310864/11/22Revised Proof10 114310244/11/22Revised Proof11 114310314/8/22Tech Review1214114310224/7/22Graphics13 114310313/29/22Tech Review1413114310863/29/22Revised Proof15 114310243/29/22Revised Proof16 114310863/28/22Revised Proof17 114310313/23/22Tech Review1810114310223/22/22Graphics19 114310863/15/22Revised Proof20 114310233/15/221st proof21 114310313/7/22Tech Review227114310223/4/22Graphics23 114310863/3/22Revised Proof24 114310243/3/22Revised Proof25 114310863/2/22Revised Proof26 114310313/2/22Tech Review273114310312/28/22Tech Review283114310212/24/22Graphics29 114310312/2/22Tech Review302114310312/2/22Tech Review312114310312/2/22Tech Review322114311242/1/22Estimating33 114310311/28/22Tech Review341114310311/21/22Tech Review351114311361/20/22Sent To Client36 114311241/20/22Estimating37 41610314/21/22Tech Review11741610224/20/22Graphics2 41610863/29/22Revised Proof3 41610223/29/22Graphics4 41610313/28/22Tech Review51441610223/25/22Graphics6 41610313/21/22Tech Review71341610863/21/22Revised Proof8 41610313/21/22Tech Review91241610863/21/22Revised Proof10 41610243/21/22Revised Proof11 41610213/18/22Graphics12 41610213/17/22Graphics13 41610213/15/22Graphics14 41611353/11/22Estimating15 41610863/9/22Revised Proof16 41610313/3/22Tech Review17541610243/1/22Revised Proof18 41611353/1/22Estimating19 41610312/4/22Tech Review20341610312/3/22Tech Review21341610312/3/22Tech Review22341610312/2/22Tech Review23341610312/1/22Tech Review24341610312/1/22Tech Review25341610312/1/22Tech Review26341610311/28/22Tech Review27341610311/25/22Tech Review28341610311/18/22Tech Review29341610311/17/22Tech Review303416103112/17/21Tech Review313416103112/8/21Tech Review323416102212/7/21Graphics33 416103112/3/21Tech Review342416106112/3/21Graphics35 416103112/2/21Tech Review361416103111/23/21Tech Review371416113311/22/21Follow up38 416113811/22/21Scheduling39 416111311/18/21Clarification40 416102111/17/21Graphics41

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" ) )

Forum Discussion

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 Review" phase. The possible soultion was provided in a previous thread which i have posted below.

How many times = 
VAR _maxIndexOf_ProjectAndStage =
    MAXX (
        FILTER (
            'Table',
            'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && 'Table'[Stage_Name] = "Tech Review"
        ),
        'Table'[ProjectIndex]
    )
VAR _maxIndexOfProject =
    MAXX (
        FILTER ( 'Table', 'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] ) ),
        'Table'[ProjectIndex]
    )
VAR _countNotTechReview =
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] )
                && 'Table'[ProjectIndex] > EARLIER ( 'Table'[ProjectIndex] )
                && 'Table'[Stage_Name] <> "Tech Review"
        )
    )
RETURN
    IF (
        'Table'[Stage_Name] = "Tech Review",
        _countNotTechReview - ( _maxIndexOfProject - _maxIndexOf_ProjectAndStage - 1 )

Below is the table results from the logic above. As you can see, it skips values in the "how many times" column. I think this is really close but I cannot figure out the last step. Thank you!

Project ID	Stage Id	Created	Stage_Name	Index	How many times "Tech Review"
690	1139	4/21/22	Prod Files	1
690	1031	4/20/22	Tech Review	2	9
690	1086	4/19/22	Revised Proof	3
690	1140	4/14/22	Revised Proof	4
690	1031	4/14/22	Tech Review	5	7
690	1086	2/22/22	Revised Proof	6
690	1031	2/20/22	Tech Review	7	6
690	1022	2/17/22	Graphics	8
690	1135	2/17/22	Estimating	9
690	1031	1/28/22	Tech Review	10	4
690	1031	1/28/22	Tech Review	11	4
690	1023	1/28/22	1st proof	12
690	1047	1/27/22	In Production	13
690	1022	1/27/22	Graphics	14
690	1031	1/27/22	Tech Review	15	1
1143	1086	4/21/22	Revised Proof	1
1143	1024	4/21/22	Revised Proof	2
1143	1135	4/19/22	Estimating	3
1143	1086	4/18/22	Revised Proof	4
1143	1024	4/18/22	Revised Proof	5
1143	1022	4/18/22	Graphics	6
1143	1031	4/18/22	Tech Review	7	17
1143	1140	4/12/22	Revised Proof	8
1143	1031	4/12/22	Tech Review	9	16
1143	1086	4/11/22	Revised Proof	10
1143	1024	4/11/22	Revised Proof	11
1143	1031	4/8/22	Tech Review	12	14
1143	1022	4/7/22	Graphics	13
1143	1031	3/29/22	Tech Review	14	13
1143	1086	3/29/22	Revised Proof	15
1143	1024	3/29/22	Revised Proof	16
1143	1086	3/28/22	Revised Proof	17
1143	1031	3/23/22	Tech Review	18	10
1143	1022	3/22/22	Graphics	19
1143	1086	3/15/22	Revised Proof	20
1143	1023	3/15/22	1st proof	21
1143	1031	3/7/22	Tech Review	22	7
1143	1022	3/4/22	Graphics	23
1143	1086	3/3/22	Revised Proof	24
1143	1024	3/3/22	Revised Proof	25
1143	1086	3/2/22	Revised Proof	26
1143	1031	3/2/22	Tech Review	27	3
1143	1031	2/28/22	Tech Review	28	3
1143	1021	2/24/22	Graphics	29
1143	1031	2/2/22	Tech Review	30	2
1143	1031	2/2/22	Tech Review	31	2
1143	1031	2/2/22	Tech Review	32	2
1143	1124	2/1/22	Estimating	33
1143	1031	1/28/22	Tech Review	34	1
1143	1031	1/21/22	Tech Review	35	1
1143	1136	1/20/22	Sent To Client	36
1143	1124	1/20/22	Estimating	37
416	1031	4/21/22	Tech Review	1	17
416	1022	4/20/22	Graphics	2
416	1086	3/29/22	Revised Proof	3
416	1022	3/29/22	Graphics	4
416	1031	3/28/22	Tech Review	5	14
416	1022	3/25/22	Graphics	6
416	1031	3/21/22	Tech Review	7	13
416	1086	3/21/22	Revised Proof	8
416	1031	3/21/22	Tech Review	9	12
416	1086	3/21/22	Revised Proof	10
416	1024	3/21/22	Revised Proof	11
416	1021	3/18/22	Graphics	12
416	1021	3/17/22	Graphics	13
416	1021	3/15/22	Graphics	14
416	1135	3/11/22	Estimating	15
416	1086	3/9/22	Revised Proof	16
416	1031	3/3/22	Tech Review	17	5
416	1024	3/1/22	Revised Proof	18
416	1135	3/1/22	Estimating	19
416	1031	2/4/22	Tech Review	20	3
416	1031	2/3/22	Tech Review	21	3
416	1031	2/3/22	Tech Review	22	3
416	1031	2/2/22	Tech Review	23	3
416	1031	2/1/22	Tech Review	24	3
416	1031	2/1/22	Tech Review	25	3
416	1031	2/1/22	Tech Review	26	3
416	1031	1/28/22	Tech Review	27	3
416	1031	1/25/22	Tech Review	28	3
416	1031	1/18/22	Tech Review	29	3
416	1031	1/17/22	Tech Review	30	3
416	1031	12/17/21	Tech Review	31	3
416	1031	12/8/21	Tech Review	32	3
416	1022	12/7/21	Graphics	33
416	1031	12/3/21	Tech Review	34	2
416	1061	12/3/21	Graphics	35
416	1031	12/2/21	Tech Review	36	1
416	1031	11/23/21	Tech Review	37	1
416	1133	11/22/21	Follow up	38
416	1138	11/22/21	Scheduling	39
416	1113	11/18/21	Clarification	40
416	1021	11/17/21	Graphics	41

Need Help

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"
        )
    )

6 Replies

AlexisOlson

Super User

4 years ago

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

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 ID	Stage Id	Created	Stage_Name	Index	How many times "Tech Review"
690	1139	4/21/2022	Prod Files	1
690	1031	4/20/2022	Tech Review	2	5
690	1086	4/19/2022	Revised Proof	3
690	1140	4/14/2022	Revised Proof	4
690	1031	4/14/2022	Tech Review	5	4
690	1086	2/22/2022	Revised Proof	6
690	1031	2/20/2022	Tech Review	7	3
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
690	1023	1/28/2022	1st proof	12
690	1047	1/27/2022	In Production	13
690	1022	1/27/2022	Graphics	14
690	1031	1/27/2022	Tech Review	15	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
1143	1031	4/18/2022	Tech Review	7	9
1143	1140	4/12/2022	Revised Proof	8
1143	1031	4/12/2022	Tech Review	9	8
1143	1086	4/11/2022	Revised Proof	10
1143	1024	4/11/2022	Revised Proof	11
1143	1031	4/8/2022	Tech Review	12	7
1143	1022	4/7/2022	Graphics	13
1143	1031	3/29/2022	Tech Review	14	6
1143	1086	3/29/2022	Revised Proof	15
1143	1024	3/29/2022	Revised Proof	16
1143	1086	3/28/2022	Revised Proof	17
1143	1031	3/23/2022	Tech Review	18	5
1143	1022	3/22/2022	Graphics	19
1143	1086	3/15/2022	Revised Proof	20
1143	1023	3/15/2022	1st proof	21
1143	1031	3/7/2022	Tech Review	22	4
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
1143	1031	3/2/2022	Tech Review	27	3
1143	1031	2/28/2022	Tech Review	28	3
1143	1021	2/24/2022	Graphics	29
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
1143	1124	2/1/2022	Estimating	33
1143	1031	1/28/2022	Tech Review	34	1
1143	1031	1/21/2022	Tech Review	35	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
416	1022	4/20/2022	Graphics	2
416	1086	3/29/2022	Revised Proof	3
416	1022	3/29/2022	Graphics	4
416	1031	3/28/2022	Tech Review	5	7
416	1022	3/25/2022	Graphics	6
416	1031	3/21/2022	Tech Review	7	6
416	1086	3/21/2022	Revised Proof	8
416	1031	3/21/2022	Tech Review	9	5
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
416	1031	3/3/2022	Tech Review	17	4
416	1024	3/1/2022	Revised Proof	18
416	1135	3/1/2022	Estimating	19
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
416	1022	12/7/2021	Graphics	33
416	1031	12/3/2021	Tech Review	34	2
416	1061	12/3/2021	Graphics	35
416	1031	12/2/2021	Tech Review	36	1
416	1031	11/23/2021	Tech Review	37	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

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"
        )
    )

Forum Discussion

Count Column by Group and filter Part 2.

6 Replies

Related content

Total Count in Column

Filter participant counts

Filter and Count

Slicer for filtering several columns

Counting nulls