topic Re: Lookup for 2 Date Range from 2 Tables in DAX Commands and Tips

Lookup for 2 Date Range from 2 Tables

o-johnralphp — Mon, 13 Oct 2025 11:16:51 GMT

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

Re: Lookup for 2 Date Range from 2 Tables

KarinSzilagyi — Mon, 13 Oct 2025 11:28:20 GMT

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?

Re: Lookup for 2 Date Range from 2 Tables

o-johnralphp — Mon, 13 Oct 2025 11:47:40 GMT

Hi @KarinSzilagyi,

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

Thanks!

Re: Lookup for 2 Date Range from 2 Tables

tayloramy — Tue, 14 Oct 2025 23:09:38 GMT

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.

Re: Lookup for 2 Date Range from 2 Tables

o-johnralphp — Tue, 14 Oct 2025 06:34:23 GMT

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.

Re: Lookup for 2 Date Range from 2 Tables

danextian — Tue, 14 Oct 2025 09:14:39 GMT

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

Re: Lookup for 2 Date Range from 2 Tables

theov — Tue, 14 Oct 2025 13:30:26 GMT

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

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

Re: Lookup for 2 Date Range from 2 Tables

tayloramy — Tue, 14 Oct 2025 23:10:55 GMT

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.

Re: Lookup for 2 Date Range from 2 Tables

o-johnralphp — Wed, 15 Oct 2025 03:40:34 GMT

Thank you very much!!!

Re: Lookup for 2 Date Range from 2 Tables

o-johnralphp — Wed, 15 Oct 2025 03:47:18 GMT

tayloramy Thank you for your great help. Much appreciated.