# Formula Filter by two variables
**URL:** https://community.fibery.io/t/formula-filter-by-two-variables/3247
**Category:** Get Help
**Created:** [September 5, 2022, 2:04pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247 "2022-09-05T14:04:07Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@avihut](https://community.fibery.io/u/avihut)
#### Post date: [September 5, 2022, 2:04pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/1 "2022-09-05T14:04:07Z")
I want to see the assigned capacity of user in a sprint, the sum of story points from all tasks a user assigned to in a given sprint.
I have a Sprint object with a one to many relationship with Tasks. Each Task can have one Sprint, and each Sprint can have many tasks. And the planning is done with a Sprint Participation database wherein each SP has one Assigned Sprint and one User.
My problem arises when I’m trying to create a formula to calculate the Assigned Capacity:
```auto
User.Tasks.Filter(Sprint.Name = [Assigned Sprint].Name)
```
But I’m getting an error that `[Assigned Sprint]` is not recognized while it is a field of Sprint Participation. A User has Tasks, and each Task is related to only one Sprint. How do I create a formula to filter a User’s Tasks per Sprint without resorting to constants in the Filter (i.e. `Sprint.Name = "Sprint 1"`)?
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [September 5, 2022, 2:20pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/2 "2022-09-05T14:20:55Z")
> [@avihut](#):
>
> But I’m getting an error that `[Assigned Sprint]` is not recognized while it is a field of Sprint Participation.
You can’t filter Tasks on a field that is part of the Sprint Capacity database.
Can you confirm what the relations are between the various databases:
Sprint 1:n Tasks
Tasks n:? User(s)
SP n:1 Sprint ([Assigned Sprint] relation)
SP n:1 User
Is this correct? Can a Task be assigned to multiple Users?
The SP database seems to exist to link Users to Sprints, implying that a User can participate in multiple Sprints.
If this is the case, when you ask
> [@avihut](#):
>
> How do I create a formula to filter a User’s Tasks per Sprint
how should the formula know which Sprint to calculate the sum of story points for?
---
### Author:  [@avihut](https://community.fibery.io/u/avihut)
#### Post date: [September 5, 2022, 2:27pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/3 "2022-09-05T14:27:16Z")
The relations are
Sprint 1:n Tasks
Tasks n:n Users
SP n:1 Sprint ([Assigned Sprint] relation)
SP n:1 User
A task indeed can be assigned to multiple users.
If use a constant like so
```auto
User.Tasks.Filter(Sprint.Name = "Sprint 1").Sum(Effort)
```
I get a correct calculation of the Assigned Effort of each User that is related to an SP, but it’s hard-coded. Is there a way to use some variable instead of `"Sprint 1"`?
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [September 5, 2022, 2:45pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/4 "2022-09-05T14:45:16Z")
> [@avihut](#):
>
> Is there a way to use some variable instead of `"Sprint 1"`?
If there is a variable, where does the value of this variable come from?
---
### Author:  [@avihut](https://community.fibery.io/u/avihut)
#### Post date: [September 5, 2022, 2:46pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/5 "2022-09-05T14:46:51Z")
From the SP object. All I want is a way to do is
```auto
User.Tasks.Filter(Sprint.Name = SP.[Assigned Sprint]).Sum(Effort)
```
And I feel that I’m missing something syntactic here.
But if the solution is changing the relationships someway I’m also open to that.
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [September 5, 2022, 2:48pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/6 "2022-09-05T14:48:19Z")
But a User is not tied to a single Sprint via the SP relation…
Sprint 1:n SP n:1 User
implies that a User can theoretically be assigned to multiple Sprints.
---
### Author:  [@avihut](https://community.fibery.io/u/avihut)
#### Post date: [September 5, 2022, 2:53pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/7 "2022-09-05T14:53:18Z")
Yeah, they indeed can. There is an inherent Sprint n:n User relation that is implemented like this
Sprint 1:n SP
SP n:1 User
The use of SP is just to have a place to manage the user’s capacity for each Sprint.
Maybe I’m overcomplicating things. Is there some other way to do something like this in Fibery?
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [September 5, 2022, 3:06pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/8 "2022-09-05T15:06:49Z")
OK, I think I realise now what you’re getting at.
I think it’s possible, but I’ll need to check first.
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [September 5, 2022, 4:11pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/9 "2022-09-05T16:11:38Z")
It’s not very pretty, but I suggest the following:
1. Create a formula field in SP which gets the number of User’s Tasks:
`User.Tasks.Count()`
2. Create a formula field in SP which gets the number of Tasks in the Sprint:
`Sprint.Tasks.Count()`
3. Create formula field in Task db which lists all the User IDs:
`If(Users.count()> 0, "<" + Users.join([Public Id],"><")+ ">","")`
4. Create a relation between SP and Task (n:n):
5. Create an automation in the SP db that will run when the ‘User task count’ or ‘Sprint task count’ is changed (indicating that the Task has been changed in some relevant way) and will populate the Tasks relation based on the following criteria:
- SP Sprint = Task Sprint
- SP User’s ID is in Task’s User ID list
The formula will be something like this:
`Tasks.Filter([Step 1 SP].Sprint = Sprint and Find([User IDs],"<"+ [Step 1 SP].User.[Public Id] + ">")>0)`
1. Create a formula that sums the effort of these Tasks:
This solution assumes that the necessary SP entities have been created before the Tasks are assigned to Users/Sprints.
As Tasks are assigned to Users and/or Sprints, the automation will get triggered and recalculate which Tasks meet the criteria.
Until we get a formula function for ‘intersection’ of collections, I think this is the best we can hope for.
Note: potentially one could combine 5 and 6 so that the total effort was directly calculated in the automation, but I think the method above enhances clarity.
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [July 3, 2023, 5:28pm UTC](https://community.fibery.io/t/formula-filter-by-two-variables/3247/10 "2023-07-03T17:28:38Z")
Just revisiting this now that ‘this’ is available in formulas.
Is it possible that you are looking for the following formula (in the SP database):
`User.Tasks.filter(Sprint= [This].Sprint).Sum(Effort)`