IntervalMatch(value) LOAD start, end ...

IntervalMatch in Power BI

IntervalMatch links values (dates or numbers) to the intervals that contain them. Power BI relationships only match equal values. Do it in Power Query, in a DAX calculated column, or by expanding intervals into one row per day.

Partial / different approach Power Query DAX

Short answer: For a few intervals, use a DAX column: MAXX(FILTER(Intervals, Start <= x && End >= x), Intervals[ID]). For many rows, expand each interval into dates in Power Query (List.Dates) and merge on the exact date.

What it does in Qlik

IntervalMatch(EventDate) LOAD Start, End RESIDENT Intervals; builds a bridge table that links each discrete value to every closed interval [Start, End] containing it. The extended syntax adds key fields, for example for price validity per product (slowly changing dimensions).

Qlik example

Qlik script

Shifts: LOAD ShiftID, Start, End FROM [lib://Data/shifts.qvd] (qvd);
Events: LOAD EventID, EventTime FROM [lib://Data/events.qvd] (qvd);

Bridge:
IntervalMatch(EventTime) LOAD Start, End RESIDENT Shifts;

Power BI equivalent

DAX calculated column on Events

DAX

ShiftID =
VAR t = Events[EventTime]
RETURN
    MAXX ( FILTER ( Shifts, Shifts[Start] <= t && Shifts[End] >= t ), Shifts[ShiftID] )

Then relate Events[ShiftID] to Shifts[ShiftID].

Power Query range lookup

Power Query M

let
    ShiftsB  = Table.Buffer(Shifts),
    WithList = Table.AddColumn(Events, "Match", (e) =>
                 Table.SelectRows(ShiftsB, each [Start] <= e[EventTime] and [End] >= e[EventTime])),
    Expanded = Table.ExpandTableColumn(WithList, "Match", {"ShiftID"})
in
    Expanded

Expand to days (scales for date intervals)

Power Query M

= Table.ExpandListColumn(
    Table.AddColumn(Prices, "Date", each List.Dates([ValidFrom], Duration.Days([ValidTo] - [ValidFrom]) + 1, #duration(1,0,0,0))),
    "Date")

Gotchas and behaviour differences

Overlaps. Qlik links an event to every matching interval. MAXX picks one, and the Power Query expand duplicates the event row. Decide which you want.
Intervals are closed at both ends in Qlik. With datetime boundaries (end 08:00 = next start 08:00), an event exactly on the boundary matches two shifts. Consider < for the end.
The nested lookup is rows × intervals. On large tables, expanding to days (or using a SQL BETWEEN join upstream) is much faster.

Official documentation

IntervalMatch (script prefix) (Qlik Help)
FILTER function (DAX) (Microsoft Learn)
Table.ExpandListColumn (Power Query M) (Microsoft Learn)
List.Dates (Power Query M) (Microsoft Learn)

[Inner|Left|Right|Outer] Join / Keep Join and Keep Qlik joins on all fields with the same name, and a plain Join is a full outer join. Power Query merges need explicit key columns and a join kind. Keep filters a table without merging, which maps to a semi-join filter or an inner merge you don't expand. Partial / different approach LOAD ... AUTOGENERATE ... WHILE ... IterNo() Master calendar (date table) The Qlik master calendar script maps to a date table built in Power Query (List.Dates) or DAX (CALENDAR), marked as a date table and related to the fact date. It is essential for every time-intelligence function. Direct equivalent $Syn tables (automatic) Synthetic keys and associative linking Qlik links tables automatically on every field with the same name and builds synthetic keys when they share several fields. Power BI doesn't link on every shared name. Relationships are model objects (autodetect may suggest some from matching names at load, so check them), use a single column and have a direction, so migrations usually remodel to a star schema. No 1:1 equivalent

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.