How to get this list of items out of two tables

[Lucas_Cuypers]

I have a table with all different machines in.
table Machines

• Serial number
• Machine Type

I have a table with my Rentals agenda.
Table Rental Agenda

• Start Rental (date)
• Stop Rental (date)
• serial number

this table contains all machines that are not available … if you can not find it in this table it means that the machine is available at that specific moment.

i want to make a search table …
with the following fields
Table Lookup

-Machine Type
-Start Date
-Stop Date

The user can make his selection of those three and then the formula looks up if there are machines available.
this search table has a virtual column where this formula is in…

Any idea on how to set this formula up ? thanks

things i have tried…

formula below gives me all machine that are not available in that time i guess when looking at the selected start & stop date compared to the existing Start and stop Rentals

select(Rental Agenda[serial number],and([Start Rental] <[_THISROW].[Start Date], [Start Rental] <[_THISROW].[Stop Date]))

select(Rental Agenda[serial number],and([Stop Rental] > [_THISROW].[Start Date], [Stop Rental] >[_THISROW].[StopDate]))

is that correct ?
i took the inverse of what i should have so these should be a list of not available machines as they come out of the agenda table

can you some how join to list without making doubles ?
This is still a list of all machine type togethers can i add this somehow in this formula so it is only giving the not available machines from a certain machine type ?

and can i than subtract those serials from the list of total machines so i get the available machines …

all help is more than welcome because i have no clue how to get there …
thanks

[Aleksi]

How about… Machines[Serial Number] - SELECT(Rental Agenda[serial number],AND([_THISROW].[Stop Date]>=[Start Rental],[_THISROW].[Start Date]<=[Stop Rental]))

[Lucas_Cuypers]

Hi Aleksi, thanks

this is already helping a lot…
any idea on how to add the machine type filter as well on it ?

[Aleksi · Accepted solution]

Like… SELECT(Machines[Serial Number],[Machine Type]=“Drill”) - SELECT(…)

[Lucas_Cuypers]

thanks, that did the job

[Aleksi]

You’re welcome