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)
I think it might be trying to remerge the data and thus filtering it ?...I have this as of now:
#"Merged Queries1" = Table.NestedJoin(#"Expanded AgileTeams", {"Parent"}, #"Expanded AgileTeams", {"Parent"}, "Expanded AgileTeams", JoinKind.LeftOuter),
#"Aggregated Expanded AgileTeams" = Table.AggregateTableColumn(#"Merged Queries1", "Expanded AgileTeams", {{"Parent", List.Count, "Count of Expanded AgileTeams.Parent"}}),
#"Merged Queries2" = Table.NestedJoin(#"Aggregated Expanded AgileTeams", {"Parent"}, #"Aggregated Expanded AgileTeams", {"Parent"}, "Aggregated Filtered Rows", JoinKind.LeftOuter),
#"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 Expanded AgileTeams",{{"Count of Expanded AgileTeams.Parent", "SolutionEpicChildCount"}})
I need to have 2 counts, 1 with count of children and 1 with count of children that are Done.
#"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.