Forum Discussion

Anonymous's avatar
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 depend...
Need Help
  • AlexisOlson's avatar
    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_ ) )
CNENFRNL's avatar
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's avatar
    Anonymous
    Not applicable
    3 years ago

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