Help with Filtering Expression

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.

2 Likes

Thank you @Marc_Dillon. Let me test that out :grinning: