Forum Discussion
Anonymous
3 years agoNot applicable
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...
- 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_ ) )
AlexisOlson
3 years agoSuper User
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_ ) )