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
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
- MattJessop7 years agoFrequent Visitor
Thank you, that's a fantastic solution.