Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have 2 tables containing "load" data; I'm trying to pull the "load id" for the closest "companyStore" load into the "agent" table but it need to be on the same date as the "agent" load. There is a "pickUp" date field in both tables.
the first contains "agent" load data
the second contains "companyStore" load data
I created a column in the "agent" table and I have been able to pull the closest location from the "companyStore", using the command below. I need it to pull the closest "companyStore" data for the same date that is provided in the "agent" data.
Closest CompanyStore Load =
VAR Lat1 = 'Agent Loads'[sh_latitude]
VAR Lng1 = 'Agent Loads'[sh_longitude]
VAR P =
DIVIDE ( PI (), 180 )
RETURN
CALCULATE (
FIRSTNONBLANK ( 'CompanyStore Loads'[loadId], 0 ),
// Arbitrary tie-break
TOPN (
1,
'CompanyStore Loads',
VAR Lat2 = 'CompanyStore Loads'[sh_latitude]
VAR Lng2 = 'CompanyStore Loads'[sh_longitude]
//---- Algorithm here -----
VAR A =
0.5 - COS ( ( Lat2 - Lat1 ) * P ) / 2
+ COS ( Lat1 * P ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2 - Lng1 ) * P ) ) / 2
VAR final =
12742 * ASIN ( ( SQRT ( A ) ) )
RETURN
final,
ASC
)
)
Hi @jhgrav3
You will need to apply the pickUp date from the current row as a filter on 'CompanyStore Loads' at some point, so that the table passed to TOPN is limited to rows from the relevant date.
Looking at the code, I took the opportunity to make some tweaks made to simplify things and hopefully improve performance.
Below is the suggested code for the calculated column, incorporating the pickUp date filter.
Closest CompanyStore Load =
VAR Lat1 = 'Agent Loads'[sh_latitude]
VAR Lng1 = 'Agent Loads'[sh_longitude]
VAR AgentLoadPickUp = 'Agent Load'[pickUp]
VAR P =
PI () / 180 -- DIVIDE not needed as constant expression
VAR CompanyStoreLoadsSummary =
-- Just required columns of 'CompanyStore Loads' on same date
CALCULATETABLE (
SUMMARIZE(
'CompanyStore Loads'[loadId],
'CompanyStore Loads'[sh_latitude],
'CompanyStore Loads'[sh_longitude]
),
-- Apply PickUp date filter
'CompanyStore Loads'[pickUp] = AgentLoadPickUp
)
RETURN
-- Arbitrary tie-break with MINX
MINX (
TOPN (
1,
CompanyStoreLoadsSummary,
VAR Lat2 = 'CompanyStore Loads'[sh_latitude]
VAR Lng2 = 'CompanyStore Loads'[sh_longitude]
//---- Algorithm here -----
VAR A =
0.5 - COS ( ( Lat2 - Lat1 ) * P ) / 2
+ COS ( Lat1 * P ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2 - Lng1 ) * P ) ) / 2
VAR final =
12742 * ASIN ( ( SQRT ( A ) ) )
RETURN
final,
ASC
),
'CompanyStore Loads'[loadId]
)
Does this work as expected?
Regards,
Owen
This certainly got me much closer to my answer. I added 1 additional filter for "Affiliate", otherwise this works. Thank you for the great help!
Closest CompanyStore Load2 =
VAR Lat1 = 'All Loads'[sh_latitude]
VAR Lng1 = 'All Loads'[sh_longitude]
VAR AgentLoadPickUp = 'All Loads'[gn_pickUp]
VAR AgentAffiliate = 'All Loads'[Affiliate]
VAR P =
PI () / 180 -- DIVIDE not needed as constant expression
VAR CompanyStoreLoadsSummary =
-- Just required columns of 'CompanyStore Loads' on same date
CALCULATETABLE (
SUMMARIZE(
'All CompanyStore Loads',[gn_id],
[Affiliate],
[sh_latitude],
[sh_longitude]
),
-- Apply PickUp date filter
'All CompanyStore Loads'[gn_pickUp] = AgentLoadPickUp,
'All companyStore Loads'[Affiliate] = AgentAffiliate
)
RETURN
-- Arbitrary tie-break with MINX
MINX (
TOPN (
1,
CompanyStoreLoadsSummary,
VAR Lat2 = [sh_latitude]
VAR Lng2 = [sh_longitude]
//---- Algorithm here -----
VAR A =
0.5 - COS ( ( Lat2 - Lat1 ) * P ) / 2
+ COS ( Lat1 * P ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2 - Lng1 ) * P ) ) / 2
VAR final =
12742 * ASIN ( ( SQRT ( A ) ) )
RETURN
final,
ASC
),
'All CompanyStore Loads' [gn_id]
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |