Forum Discussion
How do I add a filter to a Table.AggregateTableColumn
- Done Employee Count =var curParent = [Parent]
var final =
COUNTROWS(FILTER('Check Table', [Parent] = curParent && 'Check Table'[Status] = "Done" && [Parent] <> BLANK()))Return
If(Final = BLANK(), 0, Final)
#"Aggregated Filtered Rows" = Table.AggregateTableColumn(Table.SelectRows(#"Merged Queries2", each [Status]="Done"),"Aggregated Filtered Rows", {{"Parent", List.Count, "Count of Done.2"}}),
#"Renamed Columns3" = Table.RenameColumns(#"Aggregated Filtered Rows",{{"Count of Expanded AgileTeams.Parent", "SolutionEpicChildCount"}})
When I try this:
1) Merged Queries1 step contains 140 rows
2) Aggregated Expanded Agile Teams step contains 140 rows and shows the correct number of children for each
3) Merged Queiries2 step contains 140 rows
4) Aggregated Filtered Rows step only contains 10 rows (which all these rows are the ones that have status of Done)...This is the issue, I don't want to filter on only showing Done, I want all 140 rows with the additional column of Done, so 10 rows will have this new column with a value and the other 130 will have this value as null.
If needed, I could duplicate the table, filter only "Done" items, Group by Parent and then take this new Count column and merge it back to the original table, but wasn't sure if this is effiecent to do.