Forum Discussion

MattJessop's avatar
MattJessop
Frequent Visitor
7 years ago
Solved

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 po...
  • LivioLanzo's avatar
    LivioLanzo
    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