Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hello, everyone,
I have been using this formula to calculate the closest stores to my customers. I found it online on the forum and it's great.
However, I would like to add a small nuance to it: I would like to find the closes store to my customer BASED on another column first and then do the calculation. Basically I need this awesome formula to filer the stores table by the type of customer before it calculates the closest store.
I tried modifying the formula below but I am obtaining blank values for some. Can you guys help? Where to put the filter function so it can filter on the "Group1" or "Group2" before it can calculate the closest store to that customer?
Original Formula: Closest Store = VAR Lat1 = Customers[Latitude] VAR Lng1 = Customers[Longitude] VAR P = DIVIDE ( PI (), 180 ) RETURN CALCULATE ( FIRSTNONBLANK ( Stores[Store], 0 ), // Arbitrary tie-break TOPN ( 1, Stores, VAR Lat2 = Stores[Latitude] VAR Lng2 = Stores[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 ) )
Closest Store2 = VAR Lat1 = Customers[Latitude] VAR Lng1 = Customers[Longitude] VAR P = DIVIDE ( PI (), 180 ) RETURN CALCULATE ( FIRSTNONBLANK ( Stores[Store], 0 ), // Arbitrary tie-break TOPN ( 1, Stores, VAR Lat2 = Stores[Latitude] VAR Lng2 = Stores[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 ),FILTER('Stores', 'Stores'[Column 2] = EARLIER('Customers'[Column 2]) ))
Here are the two tables in question:
Customer | Latitude | Longitude | Closest Store (Original Formula) | Distance to Closest Store (km) | Closest Store2 (New formula) | Column 2 |
Customer 1 | -36.845538 | 174.760461 | Soul Bar and Bistro | 0.376745939876671 | Group1 | |
Customer 2 | -36.825887 | 174.748118 | The Hilton | 2.21773122504468 | Group1 | |
Customer 3 | -36.850022 | 174.765985 | McDonald's | 0.0625924022793106 | McDonald's | Group1 |
Customer 4 | -36.855739 | 174.766955 | The Langham | 0.320886788770884 | The Langham | Group2 |
Customer 5 | -36.858394 | 174.760322 | The Langham | 0.349053269527966 | The Langham | Group2 |
Customer 6 | -36.844891 | 174.773084 | PWC | 0.717601194877626 | Group2 |
Store | Latitude | Longitude | Column 2 |
PWC | -36.843158 | 174.765316 | Group1 |
ASB | -36.851273 | 174.764702 | Group1 |
McDonald's | -36.850191 | 174.765314 | Group1 |
The Langham | -36.857474 | 174.764073 | Group2 |
Soul Bar and Bistro | -36.843074 | 174.763367 | Group2 |
The Hilton | -36.840087 | 174.765616 | Group2 |
Solved! Go to Solution.
Hi @Anonymous
Modify with this formula
Closest Store2 = VAR Lat1 = Customers[Latitude] VAR Lng1 = Customers[Longitude] VAR P = DIVIDE ( PI (), 180 ) RETURN CALCULATE ( FIRSTNONBLANK ( Stores[Store], 0 ), // Arbitrary tie-break TOPN ( 1, FILTER(Stores,Stores[group]=Customers[group]), VAR Lat2 = Stores[Latitude] VAR Lng2 = Stores[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 @Anonymous
Modify with this formula
Closest Store2 = VAR Lat1 = Customers[Latitude] VAR Lng1 = Customers[Longitude] VAR P = DIVIDE ( PI (), 180 ) RETURN CALCULATE ( FIRSTNONBLANK ( Stores[Store], 0 ), // Arbitrary tie-break TOPN ( 1, FILTER(Stores,Stores[group]=Customers[group]), VAR Lat2 = Stores[Latitude] VAR Lng2 = Stores[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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |