match the nearest record from another table

[clteh9]

I have 2 tables and i would like to match the nearest record from another table by comparing table1's access_time and table2's permit_time and its category.  thanks for the help

table1:

item	category	access_time
1	A	2022-11-23 08:12:00
1	B	2022-11-23 09:12:00
2	C	2022-11-22 13:12:00
3	D	2022-11-22 14:12:00

table2:

item	category	permit_time	value
1	A	2022-11-23 09:12:00	11
1	A	2022-11-23 08:30:00	12
1	B	2022-11-22 08:30:00	13
2	C	2022-11-22 13:12:00	21
3	D	2022-11-22 14:12:00	31
3	D	2022-11-22 14:30:00	32

expected result:

item	category	access_time	permit time	value
1	A	2022-11-23 08:12:00	2022-11-23 08:12:00	12
1	B	2022-11-23 09:12:00	2022-11-22 08:30:00	13
2	C	2022-11-22 13:12:00	2022-11-22 13:12:00	21
3	D	2022-11-22 14:12:00	2022-11-22 14:12:00	31

[FreemanZ]

hi @clteh9 

you may also try with measures, by plotting a table visual with all table1 columns and two measures like:

permit_time2 = 
VAR _access_time=MAX(Table1[access_time])
VAR _gapmin =
    MINX(
        Table2,
        ABS(Table2[permit_time]-_access_time)
    )
VAR result =
    MAXX(
        FILTER(
           Table2,
            ABS(Table2[permit_time]-_access_time)=_gapmin
        ),
       Table2[permit_time]
    )
RETURN
    result

and

value2 = 
VAR _access_time=MAX(Table1[access_time])
VAR _gapmin =
    MINX(
        Table2,
        ABS(Table2[permit_time]-_access_time)
    )
VAR result =
    MAXX(
        FILTER(
           Table2,
            ABS(Table2[permit_time]-_access_time)=_gapmin
        ),
       Table2[value]
    )
RETURN
    result

it worked like:

[FreemanZ]

hi @clteh9 

Supposing your tables are related like:

try to add two columns one after another like:

permit_time = 
VAR _table=RELATEDTABLE(Table2)
VAR _gapmin =
    MINX(
        _table,
        ABS(Table2[permit_time]-Table1[access_time])
    )
VAR result = 
    MAXX(
        FILTER(
            _table,
            ABS(Table2[permit_time]-Table1[access_time])=_gapmin
        ),
    Table2[permit_time] 
    )
RETURN 
    result

and

value = 
VAR _table=RELATEDTABLE(Table2)
VAR result = 
    MAXX(
        FILTER(
            _table,
            Table2[permit_time]=Table1[permit_time]
        ),
    Table2[value] 
    )
RETURN
    result

it worked like: