Return load number for the nearest location on same date

[jhgrav3]

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

[OwenAuger]

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

[jhgrav3]

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