Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
Anonymous
Not applicable

Count Column by Group and filter for all variable

‎09-22-2022 08:25 AM

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 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 Review10 
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 Review27 
114310312/28/2022Tech Review283
114310212/24/2022Graphics29 
114310312/2/2022Tech Review30 
114310312/2/2022Tech Review31 
114310312/2/2022Tech Review322
114311242/1/2022Estimating33 
114310311/28/2022Tech Review34 
114310311/21/2022Tech Review351
114311361/20/2022Sent To Client36 
114311241/20/2022Estimating37 

 

 

Table[2]

Project IDStage IdCreatedStage_NameIndexHow many times Every Stage
69011394/21/2022Prod Files11
69010314/20/2022Tech Review25
69010864/19/2022Revised Proof3 
69011404/14/2022Revised Proof42
69010314/14/2022Tech Review54
69010862/22/2022Revised Proof61
69010312/20/2022Tech Review73
69010222/17/2022Graphics82
69011352/17/2022Estimating91
69010311/28/2022Tech Review10 
69010311/28/2022Tech Review112
69010231/28/20221st proof121
69010471/27/2022In Production131
69010221/27/2022Graphics141
69010311/27/2022Tech Review151
114310864/21/2022Revised Proof1 
114310244/21/2022Revised Proof26
114311354/19/2022Estimating33
114310864/18/2022Revised Proof4 
114310244/18/2022Revised Proof55
114310224/18/2022Graphics64
114310314/18/2022Tech Review79
114311404/12/2022Revised Proof83
114310314/12/2022Tech Review98
114310864/11/2022Revised Proof10 
114310244/11/2022Revised Proof114
114310314/8/2022Tech Review127
114310224/7/2022Graphics134
114310313/29/2022Tech Review146
114310863/29/2022Revised Proof15 
114310243/29/2022Revised Proof162
114310863/28/2022Revised Proof173
114310313/23/2022Tech Review185
114310223/22/2022Graphics193
114310863/15/2022Revised Proof202
114310233/15/20221st proof211
114310313/7/2022Tech Review224
114310223/4/2022Graphics232
114310863/3/2022Revised Proof24 
114310243/3/2022Revised Proof25 
114310863/2/2022Revised Proof261
114310313/2/2022Tech Review27 
114310312/28/2022Tech Review283
114310212/24/2022Graphics291
114310312/2/2022Tech Review30 
114310312/2/2022Tech Review31 
114310312/2/2022Tech Review322
114311242/1/2022Estimating332
114310311/28/2022Tech Review34 
114310311/21/2022Tech Review351
114311361/20/2022Sent To Client361
114311241/20/2022Estimating371

 

Labels:
Message 1 of 5
1,674 Views
0
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

‎09-23-2022 01:34 PM

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

View solution in original post

Message 4 of 5
1,604 Views
0
4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

‎10-03-2022 01:21 AM

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

Message 5 of 5
1,579 Views
0
AlexisOlson
Super User
Super User

‎09-23-2022 01:34 PM

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_ ) )
Message 4 of 5
1,605 Views
0
CNENFRNL
Community Champion
Community Champion

‎09-22-2022 01:25 PM

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"

CNENFRNL_0-1663878326114.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Message 2 of 5
1,628 Views
0
Anonymous
Not applicable
In response to CNENFRNL

‎09-23-2022 06:04 AM

I am not only looking to count "Tech Review" I need to count all the different stages. 

Message 3 of 5
1,619 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All