I currently have an organized table of projects as they enter and leave different stages in a gantt chart. The problem is, that a project can jump back and forth between stages and departments depending of the scope of the project. I am looking for a running count for the number of times a project has gone into ALL particular stages. Right now, I can only tell that information based on a specific stage_name. I have provided the logic below from AlexisOlson on a previous thread. The logic below, calculates the following table [1]. I need to be able to calulate table[2] so that it encompasses every stage availble. Table[2] would allow me to analyize how many times a project has entered that particular stage so i can identify bottle necks in the gantt chart.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" ) )Table[1]Project IDStage IdCreatedStage_NameIndexHow many times "Tech Review"69011394/21/2022Prod Files1 69010314/20/2022Tech Review2569010864/19/2022Revised Proof3 69011404/14/2022Revised Proof4 69010314/14/2022Tech Review5469010862/22/2022Revised Proof6 69010312/20/2022Tech Review7369010222/17/2022Graphics8 69011352/17/2022Estimating9 69010311/28/2022Tech Review10 69010311/28/2022Tech Review11269010231/28/20221st proof12 69010471/27/2022In Production13 69010221/27/2022Graphics14 69010311/27/2022Tech Review151114310864/21/2022Revised Proof1 114310244/21/2022Revised Proof2 114311354/19/2022Estimating3 114310864/18/2022Revised Proof4 114310244/18/2022Revised Proof5 114310224/18/2022Graphics6 114310314/18/2022Tech Review79114311404/12/2022Revised Proof8 114310314/12/2022Tech Review98114310864/11/2022Revised Proof10 114310244/11/2022Revised Proof11 114310314/8/2022Tech Review127114310224/7/2022Graphics13 114310313/29/2022Tech Review146114310863/29/2022Revised Proof15 114310243/29/2022Revised Proof16 114310863/28/2022Revised Proof17 114310313/23/2022Tech Review185114310223/22/2022Graphics19 114310863/15/2022Revised Proof20 114310233/15/20221st proof21 114310313/7/2022Tech Review224114310223/4/2022Graphics23 114310863/3/2022Revised Proof24 114310243/3/2022Revised Proof25 114310863/2/2022Revised Proof26 114310313/2/2022Tech Review27 114310312/28/2022Tech Review283114310212/24/2022Graphics29 114310312/2/2022Tech Review30 114310312/2/2022Tech Review31 114310312/2/2022Tech Review322114311242/1/2022Estimating33 114310311/28/2022Tech Review34 114310311/21/2022Tech Review351114311361/20/2022Sent To Client36 114311241/20/2022Estimating37 Table[2]Project IDStage IdCreatedStage_NameIndexHow many times Every Stage69011394/21/2022Prod Files1169010314/20/2022Tech Review2569010864/19/2022Revised Proof3 69011404/14/2022Revised Proof4269010314/14/2022Tech Review5469010862/22/2022Revised Proof6169010312/20/2022Tech Review7369010222/17/2022Graphics8269011352/17/2022Estimating9169010311/28/2022Tech Review10 69010311/28/2022Tech Review11269010231/28/20221st proof12169010471/27/2022In Production13169010221/27/2022Graphics14169010311/27/2022Tech Review151114310864/21/2022Revised Proof1 114310244/21/2022Revised Proof26114311354/19/2022Estimating33114310864/18/2022Revised Proof4 114310244/18/2022Revised Proof55114310224/18/2022Graphics64114310314/18/2022Tech Review79114311404/12/2022Revised Proof83114310314/12/2022Tech Review98114310864/11/2022Revised Proof10 114310244/11/2022Revised Proof114114310314/8/2022Tech Review127114310224/7/2022Graphics134114310313/29/2022Tech Review146114310863/29/2022Revised Proof15 114310243/29/2022Revised Proof162114310863/28/2022Revised Proof173114310313/23/2022Tech Review185114310223/22/2022Graphics193114310863/15/2022Revised Proof202114310233/15/20221st proof211114310313/7/2022Tech Review224114310223/4/2022Graphics232114310863/3/2022Revised Proof24 114310243/3/2022Revised Proof25 114310863/2/2022Revised Proof261114310313/2/2022Tech Review27 114310312/28/2022Tech Review283114310212/24/2022Graphics291114310312/2/2022Tech Review30 114310312/2/2022Tech Review31 114310312/2/2022Tech Review322114311242/1/2022Estimating332114310311/28/2022Tech Review34 114310311/21/2022Tech Review351114311361/20/2022Sent To Client361114311241/20/2022Estimating371

Anonymous

Not applicable

3 years ago

Solved

Count Column by Group and filter for all variable

I currently have an organized table of projects as they enter and leave different stages in a gantt chart. The problem is, that a project can jump back and forth between stages and departments depending of the scope of the project.

I am looking for a running count for the number of times a project has gone into ALL particular stages. Right now, I can only tell that information based on a specific stage_name. I have provided the logic below from AlexisOlson on a previous thread. The logic below, calculates the following table [1]. I need to be able to calulate table[2] so that it encompasses every stage availble. Table[2] would allow me to analyize how many times a project has entered that particular stage so i can identify bottle necks in the gantt chart.

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

Table[1]

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
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
1143	1031	2/28/2022	Tech Review	28	3
1143	1021	2/24/2022	Graphics	29
1143	1031	2/2/2022	Tech Review	30
1143	1031	2/2/2022	Tech Review	31
1143	1031	2/2/2022	Tech Review	32	2
1143	1124	2/1/2022	Estimating	33
1143	1031	1/28/2022	Tech Review	34
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

Table[2]

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

Need Help

AlexisOlson

3 years ago

A small update to my prior solution almost gets you there. (Swap out the hardcoded stage name with _CurrName.)

VAR _CurrPID   = 'Table2'[Project ID]
VAR _CurrIndex = 'Table2'[Index]
VAR _CurrName  = 'Table2'[Stage_Name]
VAR _SubTable_ =
    FILTER (
        'Table2',
        'Table2'[Project ID] = _CurrPID
            && 'Table2'[Index] >= _CurrIndex
            && 'Table2'[Stage_Name] = _CurrName
    )
RETURN
    COUNTROWS (
        FILTER (
            _SubTable_,
            VAR _Stage = 'Table2'[Stage_Name]
            VAR _NextName =
                LOOKUPVALUE (
                    'Table2'[Stage_Name],
                    'Table2'[Project ID], _CurrPID,
                    'Table2'[Index], Table2[Index] + 1
                )
            RETURN
                _NextName <> _CurrName
        )
    )

To get the blank rows for the repeats try something like this:

VAR _CurrPID = 'Table2'[Project ID]
VAR _CurrIndex = 'Table2'[Index]
VAR _CurrName = 'Table2'[Stage_Name]
VAR _SubTable_ =
    ADDCOLUMNS (
        FILTER (
            'Table2',
            'Table2'[Project ID] = _CurrPID
                && 'Table2'[Index] >= _CurrIndex
                && 'Table2'[Stage_Name] = _CurrName
        ),
        "NextName",
            LOOKUPVALUE (
                'Table2'[Stage_Name],
                'Table2'[Project ID], _CurrPID,
                'Table2'[Index], 'Table2'[Index] + 1
            )
    )
VAR _StageChanges_ = FILTER ( _SubTable_, [NextName] <> _CurrName )
VAR _IsRepeat = ISEMPTY ( FILTER ( _StageChanges_, [Index] = _CurrIndex ) )
RETURN
    IF ( _IsRepeat, BLANK (), COUNTROWS ( _StageChanges_ ) )

4 Replies

CNENFRNL

Community Champion

3 years ago

PQ solution with way much easier logic,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZXNbsJADIRfJeKMtLG95OdctVVvVcsNcUCQlkgUEKTt63dDCOxuxqCekijfOmOP7cxmo6xMR+MRkZTuYg2T4ZTZ3b8edqvkqd5Ux/b9aD6+sKlQx6Y9O62W6+St+qmrX/fEAVxkJ5jKHm65Y7VK3Ad2H+5ZfJxs2uFWwy2ScsVDKZOhFDbMWuxsGJvVNPMAPgFsKO/h58Niv66XbfWKIEOZROTjsam/Fk29/XQP5VCCs6TAEij9Fx26yBLRdGyS/bkSFJpo8469aH7ZtkVbfS+berdt38mwGj7vVYOAgz4aie4sdI0hfjtd+zT2kEKe7R2eA75zx2/XwB2BYqi43ayxGJ2Pkj0xPu+VMQvRfhAU7/Mwy37K1EkocHTG0UtcFt2jFNdFP0BQj9boDMsIm1FAYDFcKpEtyNTHB8JjR+29Axn+gtoylOMMRMmgALURbyf6xUG+iqGJOkuxrxId8FcMI0/FKHuAkadiLJDNaErFiKoaTektHq0kR6s8GlWPjxJFfrK61Dn284zDwpQ4NI4s6b9o5KZOR1v3VHL3T8RLF82o/psTi3FS8NBNkqzDL3/992rbJNNd8rCp3V17IgPi/ROheufn/A8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Stage Id" = _t, Created = _t, Stage_Name = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Stage Id", Int64.Type}, {"Created", type date}, {"Stage_Name", type text}, {"Index", Int64.Type}}),

    Reversed = Table.ReverseRows(#"Changed Type"),
    #"Grouped by stage" = Table.SelectRows(Table.Group(Reversed, {"Project ID","Stage_Name"}, {"Grouped by Stage", each _}, 0), each [Stage_Name]="Tech Review"),
    #"Grouped by ID" = Table.Group(#"Grouped by stage", "Project ID", {"Group by ID", each Table.AddIndexColumn(_,"Cnt",1,1)}),
    #"Expanded Group by ID" = Table.ExpandTableColumn(#"Grouped by ID", "Group by ID", {"Grouped by Stage", "Cnt"}),
    #"Expanded Grouped by Stage" = Table.ExpandTableColumn(#"Expanded Group by ID", "Grouped by Stage", {"Stage Id", "Created", "Stage_Name", "Index"})
in
    #"Expanded Grouped by Stage"

Anonymous
Not applicable
3 years ago
I am not only looking to count "Tech Review" I need to count all the different stages.

AlexisOlson

Super User

3 years ago

A small update to my prior solution almost gets you there. (Swap out the hardcoded stage name with _CurrName.)

VAR _CurrPID   = 'Table2'[Project ID]
VAR _CurrIndex = 'Table2'[Index]
VAR _CurrName  = 'Table2'[Stage_Name]
VAR _SubTable_ =
    FILTER (
        'Table2',
        'Table2'[Project ID] = _CurrPID
            && 'Table2'[Index] >= _CurrIndex
            && 'Table2'[Stage_Name] = _CurrName
    )
RETURN
    COUNTROWS (
        FILTER (
            _SubTable_,
            VAR _Stage = 'Table2'[Stage_Name]
            VAR _NextName =
                LOOKUPVALUE (
                    'Table2'[Stage_Name],
                    'Table2'[Project ID], _CurrPID,
                    'Table2'[Index], Table2[Index] + 1
                )
            RETURN
                _NextName <> _CurrName
        )
    )

To get the blank rows for the repeats try something like this:

VAR _CurrPID = 'Table2'[Project ID]
VAR _CurrIndex = 'Table2'[Index]
VAR _CurrName = 'Table2'[Stage_Name]
VAR _SubTable_ =
    ADDCOLUMNS (
        FILTER (
            'Table2',
            'Table2'[Project ID] = _CurrPID
                && 'Table2'[Index] >= _CurrIndex
                && 'Table2'[Stage_Name] = _CurrName
        ),
        "NextName",
            LOOKUPVALUE (
                'Table2'[Stage_Name],
                'Table2'[Project ID], _CurrPID,
                'Table2'[Index], 'Table2'[Index] + 1
            )
    )
VAR _StageChanges_ = FILTER ( _SubTable_, [NextName] <> _CurrName )
VAR _IsRepeat = ISEMPTY ( FILTER ( _StageChanges_, [Index] = _CurrIndex ) )
RETURN
    IF ( _IsRepeat, BLANK (), COUNTROWS ( _StageChanges_ ) )

v-yanjiang-msft
Community Support
3 years ago
Hi Anonymous ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

Forum Discussion

Count Column by Group and filter for all variable

4 Replies

Related content

Dynamic Variable in Calculated Columns

Slicer for filtering several columns

Variable still filtered in visual where using REMOVEFILTERS

Filter table based on variable table column

Filter column only if variable is not blank