Lookup for 2 Date Range from 2 Tables

[o-johnralphp]

Hello everyone!

Im trying to get the Event in Activity Table through a calculated column if the Activity Start and End is within the range of those on the Event Table.

Thanks for the help!

Activity Table
Activity	Start	End	Event (Desired Output)
1	07/03/2023 9:42	07/03/2023 23:59	No Event
2	05/01/2025 13:56	05/01/2025 23:59	A
3	08/01/2025 11:14	08/01/2025 20:15	D

Event Table
Event	Start	End
A	05/01/2025 14:56	05/01/2025 17:00
B	07/05/2023 9:42	07/05/2023 23:59
C	07/06/2023 7:00	07/06/2023 9:45
D	08/01/2025 11:14	08/01/2025 23:15

[tayloramy · Accepted solution]

Hi @o-johnralphp,

You’re matching each Activity to the single Event whose time window fully contains the Activity window (Event.Start <= Activity.Start and Event.End >= Activity.End). A calculated column on Activity is fine for this.

Below are two DAX options:

• Containment match (your stated requirement): Activity must be entirely within the Event.
• Overlap match (if you later decide any overlap counts): Activity overlaps Event in any way.

I’ve also added a simple tie-breaker in case multiple Events fit: pick the one with the earliest Event.Start. You can tweak that if you prefer a different rule.

Event (calculated column) =
VAR aStart = Activity[Start]
VAR aEnd   = Activity[End]
VAR Candidates =
    FILTER (
        Event,                               -- or 'Events' if that's your table name
        Event[Start] <= aStart
            && Event[End] >= aEnd            -- full containment
    )
VAR FirstMatch =
    TOPN ( 1, Candidates, Event[Start], ASC )   -- tie-break: earliest Event.Start
RETURN
COALESCE (
    CALCULATE ( SELECTEDVALUE ( Event[Event] ), FirstMatch ),
    "No Event"
)

If instead you want any overlap (not full containment), change the FILTER to:

FILTER (
    Events,
    Events[Start] < aEnd
        && Events[End] > aStart            -- any overlap
)

 

• Why a calculated column? You want a stable row-by-row lookup at refresh time. This avoids context complications from visuals.
• Why not LOOKUPVALUE? LOOKUPVALUE does equality matches; your requirement uses inequalities (<=, >=), so we filter Events instead.
• Tie-breaking: If multiple Events qualify, TOPN(1, ..., Events[Start], ASC) picks the earliest-starting Event. Replace with Events[End], DESC (latest ending) or another column if you prefer.
• No match: COALESCE(..., "No Event") outputs “No Event” when no Event window contains the Activity window.
• Performance tip: If Events is large, consider indexing/sorting in Power Query or materializing ranges (e.g., date keys) to reduce per-row scans. But for a modest Events table this pattern is fine.

EDIT: 
Fixed the issue of the measure returning a table instead of a value for the calculate. 
Here is a link to the working file: https://drive.google.com/file/d/1e7219J5pMPaabFfmaJsxiXJsztwrxdki/view?usp=sharing

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[theov]

This might help you, it is showing how to differrent time ranges: 

https://youtu.be/TF0lPIYjJfs?si=OXWFJrFYG9NlbZ0W

[danextian · Accepted solution]

Hi @o-johnralphp 

try this:

Event = 
VAR _start = Activity[Start]
VAR _end = Activity[End]
VAR filteredTable =
    FILTER ( Events, Events[Start] <= _end && Events[End] >= _start )
VAR _TopN =
    --sort by [Start] in ascending order if there are multiple records then select the first one
    TOPN ( 1, filteredTable, [Start], ASC ) 
RETURN
    COALESCE ( MINX ( _TopN, [Event] ), "No event" )

[o-johnralphp]

Thank you very much!!!

[tayloramy · Accepted solution]

Hi @o-johnralphp,

You’re matching each Activity to the single Event whose time window fully contains the Activity window (Event.Start <= Activity.Start and Event.End >= Activity.End). A calculated column on Activity is fine for this.

Below are two DAX options:

• Containment match (your stated requirement): Activity must be entirely within the Event.
• Overlap match (if you later decide any overlap counts): Activity overlaps Event in any way.

I’ve also added a simple tie-breaker in case multiple Events fit: pick the one with the earliest Event.Start. You can tweak that if you prefer a different rule.

Event (calculated column) =
VAR aStart = Activity[Start]
VAR aEnd   = Activity[End]
VAR Candidates =
    FILTER (
        Event,                               -- or 'Events' if that's your table name
        Event[Start] <= aStart
            && Event[End] >= aEnd            -- full containment
    )
VAR FirstMatch =
    TOPN ( 1, Candidates, Event[Start], ASC )   -- tie-break: earliest Event.Start
RETURN
COALESCE (
    CALCULATE ( SELECTEDVALUE ( Event[Event] ), FirstMatch ),
    "No Event"
)

If instead you want any overlap (not full containment), change the FILTER to:

FILTER (
    Events,
    Events[Start] < aEnd
        && Events[End] > aStart            -- any overlap
)

 

• Why a calculated column? You want a stable row-by-row lookup at refresh time. This avoids context complications from visuals.
• Why not LOOKUPVALUE? LOOKUPVALUE does equality matches; your requirement uses inequalities (<=, >=), so we filter Events instead.
• Tie-breaking: If multiple Events qualify, TOPN(1, ..., Events[Start], ASC) picks the earliest-starting Event. Replace with Events[End], DESC (latest ending) or another column if you prefer.
• No match: COALESCE(..., "No Event") outputs “No Event” when no Event window contains the Activity window.
• Performance tip: If Events is large, consider indexing/sorting in Power Query or materializing ranges (e.g., date keys) to reduce per-row scans. But for a modest Events table this pattern is fine.

EDIT: 
Fixed the issue of the measure returning a table instead of a value for the calculate. 
Here is a link to the working file: https://drive.google.com/file/d/1e7219J5pMPaabFfmaJsxiXJsztwrxdki/view?usp=sharing

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[o-johnralphp]

@tayloramy Thank you for your great help. Much appreciated.

[o-johnralphp]

Hi @tayloramy 

Thanks for the reply. I'm getting an error "The expression refers to multiple columns.Multiple columns cannot converted to a scalar value". I already formatted the necessary columns as Date/Time format.

[tayloramy]

Hi @o-johnralphp, 

I have edited my reponse to correct that. I was working with a subset of data, only one event and one activity, so my results only returned one record as there was only one record to return. Note to self: Use more complete sample data. 

Here is a link to the PBIC file with a working solution: https://drive.google.com/file/d/1e7219J5pMPaabFfmaJsxiXJsztwrxdki/view?usp=sharing

 If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[KarinSzilagyi]

Hi @o-johnralphp, what should be the output if more than one event is found between the Start- and End-DateTime of your Activity Table?

[o-johnralphp]

Hi @KarinSzilagyi,

If more than one event is found then the earliest event would be the output.

Thanks!