Forum Discussion
Combine two tables
Hi all,
I want to calculate the distance between two tables, so that we can analyse our hub locations and if we have positioned them to provide the shortest delivery times. Both tables contain postcode, latitude and longitude. I already know how to calculate the distance between two distances within the same table, however I am struggling to structure the data as I want it. I want to create a third table, that contains all hubs, and all deliveries
Ideally, this should be done as a calculated table, rather than a measure, as I'm expecting it to be quite intensive to calculate when we run large sets of data through it, and would rather handle this offline to make adjusting the data online a lot easier.
The two data tables we have are structured as follows:
| Table 1 | Postcode | Latitude | Longitude |
| Hub 1 | AB1 2DE | 56.00301 | -3.59004 |
| Hub 2 | FG3 4HI | 55.23307 | -2.96854 |
| Hub 3 | JK5 6LM | 57.10344 | -7.41123 |
| Table 2 | Postcode | Latitude | Longitude |
| Delivery 1 | NE1 0EG | 56.00301 | -3.59004 |
| Delivery 2 | BT47 1AG | 55.23307 | -2.96854 |
| Delivery 3 | TS12 1DT | 57.10344 | -7.41123 |
This is ideally how the data should come out.
| Calculated Table 3 | Hub 1 | Hub 2 | Hub 3 |
| Delivery 1 | 56.30493 | 134.34033 | 39.43034 |
| Delivery 2 | 102.34434 | 29.04334 | 85.32343 |
| Delivery 3 | 92.4034 | 43.40304 | 122.30232 |
I know that I could create a new table based off Table 2 and create calculated columns for each hub, however as we have 50+ of these this would be quite tedious and would need to be manually updated everytime we changed the dataset.
You could do something like this:
Initial Tables:
Table1:
Table2:
Calculate Table DAX:
Table =
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATE (
SELECTCOLUMNS (
Table1,
"Table1", Table1[Table 1],
"Latitude From", Table1[Latitude],
"Longitude From", Table1[Longitude]
),
SELECTCOLUMNS (
Table2,
"Table2", Table2[Table 2],
"Latitude To", Table2[Latitude],
"Longitude To", Table2[Longitude]
)
),
"Distance",
VAR Pie =
DIVIDE ( PI (), 180 )
VAR Arc =
0.5
- COS ( ( [Latitude To] - [Latitude From] ) * Pie )
/ 2
+ COS ( [Latitude From] * Pie )
* COS ( [Latitude To] * Pie )
* (
1
- COS ( ( [Longitude To] - [Longitude From] ) * Pie )
)
/ 2
VAR KMDistance =
12742 * ASIN ( SQRT ( Arc ) )
RETURN
KMDistance
),
"HUB", [Table1],
"Delivery", [Table2],
"Distance KM", [Distance]
)and at the end have:
Which will make it easier to filter your model by closet distance from each HUB
4 Replies
Hi MattJessop
how do you come up with the values in the Table 3? Is it the distance in Kilometers / Miles ? because I do not tie if on my side if these are KM or miles
Hi Livio, the figures output would be KM, although I'd advise that all of the above is sample/junk data just to illustrate it. I already have got distance calculations working for them, I'm just struggling to get the tables to merge in the way I'd like.
You could do something like this:
Initial Tables:
Table1:
Table2:
Calculate Table DAX:
Table =
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATE (
SELECTCOLUMNS (
Table1,
"Table1", Table1[Table 1],
"Latitude From", Table1[Latitude],
"Longitude From", Table1[Longitude]
),
SELECTCOLUMNS (
Table2,
"Table2", Table2[Table 2],
"Latitude To", Table2[Latitude],
"Longitude To", Table2[Longitude]
)
),
"Distance",
VAR Pie =
DIVIDE ( PI (), 180 )
VAR Arc =
0.5
- COS ( ( [Latitude To] - [Latitude From] ) * Pie )
/ 2
+ COS ( [Latitude From] * Pie )
* COS ( [Latitude To] * Pie )
* (
1
- COS ( ( [Longitude To] - [Longitude From] ) * Pie )
)
/ 2
VAR KMDistance =
12742 * ASIN ( SQRT ( Arc ) )
RETURN
KMDistance
),
"HUB", [Table1],
"Delivery", [Table2],
"Distance KM", [Distance]
)and at the end have:
Which will make it easier to filter your model by closet distance from each HUB
