Forum Discussion

clteh9's avatar
clteh9
Icon for Helper I rankHelper I
3 years ago

match the nearest record from another table

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 ...
Need Help
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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: