Lookup for 2 Date Range from 2 Tables

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.

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" )