Something like LOOKUP with conditions

[Gorgmaster]

Hey there!

I've got a little Problem that I'm not able to solve. I need something like a LOOKUP with checking conditions. Like if something is between to time borders.

Here a little example:

I've got a first table with types and times. The table shows which type was produced during which time  period. So we have a start and an end.

Types	Start	End
Type 1	01-01-2019 07:00	01-02-2019 16:00
Type 2	01-02-2019 17:00	01-03-2019 08:00
Type 3	01-03-2019 09:00	01-04-2019 22:00

In a second table I've got events with a time stamp. Something like this:

Event	Time
Event 1	01-01-2019 15:33
Event 2	01-03-2019 06:18
Event 3	01-03-2019 18:47

What I want to do is to combine these to tables. As an output I want the second table with the information which type was produced, when the event happend.

Output:

Event	Time	Type
Event 1	01-01-2019 15:33	Type 1
Event 2	01-03-2019 06:18	Type 2
Event 3	01-03-2019 18:47	Type 3

So I've tried lookup and some if-conditions but always came to the same end: it doesn't work 😞

So anybody here who can help me?

[AlB · Accepted solution]

Hi @Gorgmaster 

You can create a calculated column in the second table you show:

NewColType =
CALCULATE (
    DISTINCT ( Table1[Types] ),
    FILTER ( Table1, Table2[Time] >= Table1[Start] && Table2[Time] < Table1[End] )
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Gorgmaster]

Thanks a lot! That was the solution I needed.