Hi. I am trying to work out an expression to achieve the following
I have a table called Employees with column [Username].
I have another table called Routings_Master with columns [Routing Date] and [Resource].
And another table called Resource_Planner_Master with columns [Name], [Date From] & [Date To].
The flow would be to enter a record into the Resource_Planner_Master table first with values in the 3 columns above. I would then go to enter a record into the Routings_Master table. When I select the [Routing Date] I want my expression in the [Resource Column] to return the list of [Username]s from the Employees table excluding the names in those records of the Resource_Planner_Master table where the [Date From] to [Date To] range clash with the date of the [Routings Date] column i.e. [Routings Date] falls between [Date From] and [Date To].
Hope this makes sense. Any help would be great, thank you.
How about this?
Employees[Username]
-
SELECT( Routings_Planner_Master[Name] ,
AND( [Date From] <= [_THISROW].[Routing Date] ,
[Date To] >= [_THISROW].[Routing Date]
)
)
This expression starts with the list of all Employee Usernames, then subtracts off any Names that exist in Planner_Master records that have a conflicting date in the current record.
Might need some tweaking.
Thank you @Marc_Dillon. Let me test that out
User | Count |
---|---|
41 | |
28 | |
28 | |
23 | |
13 |