Hello community!
I am having a problem filtering rows, retrieved via linked rows in the same table.
First, some context.
I’m working on a project management system based on the Theory of Constraints. I have a project plan that has connected tasks (predecessor to successor), a critical chain (longest path of tasks) and so called “feeding chains” that are like branches, inflowing into critical chain.
I need feeding chains to bias to the right (late start), therefore I need a deadline for them to stick to.
In order to calculate the deadline for a feeding chain I need a date from the critical chain, that is not affected by the feeding chain, otherwise I get a cyclical reference.
See illustration.
I’ve written a formula running on a feeding chain rightmost task (task E) that:
- Looks into a successor of the task (task C),
- retrieves it’s predecessors (task B, E),
- filters that list to get a predecessor that is NOT on a feeding chain (I’m expecting it to be task B)
- retrieves it’s due date
Here’s the screenshot of the formula:
I’m having problems with step 3: filter doesn’t work. I successfully retrieve a list of tasks (B, E), but then I can’t filter it. I tried different operators: “!=”, “Contains()”, “Contains().Not()” etc., but none of them worked. I also tried using “Current value” in Filter( ) formula, it didn’t help.
Do you have any ides on what I’m doing wrong?