Forum Discussion
Combine two tables
- 7 years ago
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
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
Thank you, that's a fantastic solution.