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_ ) )
v-yanjiang-msft
3 years agoCommunity Support
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