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...
  • AlexisOlson's avatar
    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_ ) )