FILTER
FILTER(set, condition)
Keeps the rows of a set where a condition is TRUE, such as the predecessors that aren't complete yet. The condition must be about the set you are filtering, written through that set's name: FILTER(Predecessors, Predecessors[Status] <> "Complete"). A bare [Status] always means the row you are on, never the row being tested, which is exactly what lets a filter compare other rows with this one.
Arguments
| Argument | What to give it |
|---|---|
| set | A set of rows, like Predecessors, Upstream, All or Assignments, or a column read from one, like Predecessors[Duration]. |
| condition | One TRUE or FALSE per row of the set, read through the same set: Upstream[Critical], All[Phase] = [Phase]. Rows where it is FALSE, blank or an error are dropped. |
Examples
Formula=COUNT(FILTER(Predecessors, Predecessors[Status] <> "Complete"))
How many of the task's predecessors are still open.
Formula=SUM(FILTER(Upstream, Upstream[Phase] <> [Phase])[Remaining duration])
The remaining work upstream of the task that belongs to other phases: the condition compares each upstream task with this one.
Formula=COUNT(FILTER(Assignments, Assignments[Resource] = "Crane"))
How many crane assignments the task has.
Measure (on the project)=COUNT(FILTER(Tasks, Tasks[Total float] < 0d))
As a measure: how many tasks in the project have negative float.
Reach for something else when
| You want | Use |
|---|---|
| Whether any row matches, not the rows | ANY on the condition: ANY(Predecessors[Critical]) |
| Every row whose field equals this row's | SAME |
| One row of the result | FIRST, ONLY, or ARGMIN / ARGMAX |
| Rows in one set and not another | EXCEPT |
| Whether anything is left | ISEMPTY |
Common questions
Why does the editor say “The condition must be about Predecessors, one TRUE/FALSE per row”?
The condition reads the row you are on, not each predecessor. [Status] is this task's status, the same for every predecessor, so it can't pick some of them.
=COUNT(FILTER(Predecessors, [Status] <> "Complete"))
Read the field through the set instead: Predecessors[Status] <> "Complete".
Is there a COUNTIF or SUMIF?
No. Filter, then count or add up: COUNT(FILTER(All, All[Phase] = "Civil")) is Excel's COUNTIF, and SUM(FILTER(All, All[Phase] = "Civil")[Duration]) its SUMIF.
What happens when nothing matches?
You get an empty set, not an error. COUNT of it is 0, SUM of a column of it is 0, and MAX of a column of it is blank.
Can I filter the result of a filter?
Yes, but the second condition must be about the first filter's result. Name it with LET so you can write it once: LET(u, FILTER(Upstream, Upstream[Critical]), COUNT(FILTER(u, u[Phase] = "Civil"))). Usually it is simpler to join the conditions with AND.