Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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:
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:
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |