Combine two tables

[MattJessop]

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.

[LivioLanzo]

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

[MattJessop]

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.

[LivioLanzo · Accepted solution]

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

[MattJessop]

Thank you, that's a fantastic solution.