Forum Discussion
Solved
How do I add a filter to a Table.AggregateTableColumn
hi, I have the following, which is working as expected: = Table.AggregateTableColumn(#"Merged Queries1", "Expanded AgileTeams", {{"Parent", List.Count, "Count of Expanded AgileTeams.Parent"}})...
- 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)
Hi EaglesTony
Does this have to be done in the Query Editor?
If you do it in the front end the DAX would be much easier:
Employee Count =
var curParent = [Parent]
RETURN
COUNTROWS(FILTER('Check Table', [Parent] = curParent))
Done Employee Count =
var curParent = [Parent]
RETURN
COUNTROWS(FILTER('Check Table', [Parent] = curParent && 'Check Table'[Status] = "Done"))
This would normally work, however the table I am using has 4 types of records in a flatten scenerio, such as:
Region Mgr
---District Mgr
----Mgr
------Employee
Is there a way to only COUNTROWS if the parent is not null ?
Hi EaglesTony
Sure it would be:
Table.AggregateTableColumn(#"Merged Queries", "Filtered Rows", {{"Employee", List.Count, "Count of Employee"},{"Status", List.NonNullCount, "Count of Employee2"}})Ahh sorry you meant in the DAX sure:
Employee Count =var curParent = [Parent]RETURNCOUNTROWS(FILTER('Check Table', [Parent] = curParent))Done Employee Count =var curParent = [Parent]RETURNCOUNTROWS(FILTER('Check Table', [Parent] = curParent && 'Check Table'[Status] = "Done" && [Parent] <> BLANK()))
