Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jhgrav3
Frequent Visitor

Return load number for the nearest location on same date

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
)
)


2 REPLIES 2
OwenAuger
Super User
Super User

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.

  • The variable CompanyStoreLoadsSummary includes just loadId, sh_latitude and sh_longitude columns, and is filtered based on pickUp date. 
  • This table is passed to TOPN, then MINX finds the smallest loadId to break ties (same as FIRSTNONBLANK was doing before).
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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

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]
	)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.