Calculated Column - How to Select Row Values

[Dean_Stanberry]

I'm new to Power BI and DAX...  I'm trying to create a calculated column that computes the distance between two points using latitude/longitude.  

I have a primary table with address information including zip codes and a lookup table with all US Zip Codes and associated latitude/longitude data.  I have a What-If Parameter to get User input for a single Zip Code value to compute the distances from that point to all other locations in the primary table.  I can't seem to figure out how to select the zip code value from each row in the table for the calculation.  The code I borrowed/adapted for the calculation is below (suspect values are underlined/bold below);

Kilometers = 
var Lat1 = LOOKUPVALUE(ZipCodes[Zip_Latitude],ZipCodes[Zip_Code], 'Recruiting Test-2'[Zip_Code])
var Lng1 = LOOKUPVALUE(ZipCodes[Zip_longitude],ZipCodes[Zip_Code], 'Recruiting Test-2'[Zip_Code])

var Lat2 = LOOKUPVALUE(ZipCodes[Zip_Latitude],ZipCodes[Zip_Code],From_Zip[From_Zip Value])
var Lng2 = LOOKUPVALUE(ZipCodes[Zip_longitude],ZipCodes[Zip_Code],From_Zip[From_Zip Value])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
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

I'm attempting to use the LOOKUPVALUE to set the latitude & longitude for each row of the table (Lat1 & Lng1), but it doesn't seem to be selecting the zip code from each row.  The function does not return an error, but all of the values in the column are Zero.

Any help would be greatly appreciated...

[v-piga-msft]

Hi @Dean_Stanberry,

Have you solved the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data smaple and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Seward12533]

Hi columns are calculated once when the workbook is opened or data is refreshed. This is BEFORE the user has a chance to interact with any slicers and is not affected by any dynamic cross filtering. You need to implement as a measure.

Be sure that your lookup is disconnected l, your zip code lookup table needs to be related to your fact table by zip code and you may need to block the measure with ISFILTERED. Or HASONEVALUE so it only calculates in visuals that are filtered to ask glee zil code.

Kilometers =
var Lat1 = LOOKUPVALUE(ZipCodes[Zip_Latitude],ZipCodes[Zip_Code], 'Recruiting Test-2'[Zip_Code])
var Lng1 = LOOKUPVALUE(ZipCodes[Zip_longitude],ZipCodes[Zip_Code], 'Recruiting Test-2'[Zip_Code])

var Lat2 = MAX((ZipCodes[Zip_latitude])
var Lng2 = MAX((ZipCodes[Zip_longitude])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
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 IF(HASONEVALUE(From_Zip[From_Zip Value]),final)

And then build a max trick or table visual with the names in the cities or locations in the fromzip table with your measure as one of the values.