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)
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()))This works, sort of..It does give me now the correct number when there are children, however when there aren't it gives me I think a null, as my visual is showing a blank, so I need to somehow replace it with 0, as I want to show a 0.
- 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)