When creating a 'new column' in a Power BI table, can my dax formula run a comparison with all rows of another (small) table?

Question

I have a large (live) table in Power BI which includes easting/northing location data. (Eastings and northings are distances in metres from a fixed origin, so form an x-y grid).

I also have a smaller table with a finite number (~20) of fixed locations in easting/northing format.

I'd like to add a column in the large table to show the distance to the closest point in the smaller table. (This is done using Pythagoras: sqrt(Δeasting²+Δnorthing²)).

Any help with this would be much appreciated. Thanks.

Edit - example data:

large_table

eastlarge	northlarge
104	103
205	212
209	240
328	345

small_table

eastsmall	northsmall
100	100
200	200
300	300

I can manually calculate the new column in large_table, but obviously I need to implement a formula:

large_table

eastlarge	northlarge	min_distance
104	103	5
205	212	13
209	240	41
328	345	53

Answer 1

Here is a calculated column:

Minimum Distance = 
VAR _easting = large_table[eastlarge]
VAR _northing = large_table[northlarge]
RETURN
MINX ( 
    ADDCOLUMNS ( 
        small_table ,
        "dist" , SQRT ( ( small_table[eastsmall] - _easting )^2 + ( small_table[northsmall] - _northing )^2 )
    ),
    [dist]
)

Result:

You can also use a measure, using the same code with small adjustments:

Min Distance := 
VAR _easting = SELECTEDVALUE ( large_table[eastlarge] )
VAR _northing = SELECTEDVALUE ( large_table[northlarge] )
RETURN
MINX ( 
    ADDCOLUMNS ( 
        small_table ,
        "dist" , SQRT ( ( small_table[eastsmall] - _easting )^2 + ( small_table[northsmall] - _northing )^2 )
    ),
    [dist]
)

Looks like this:

Answer 2

You can do this in power query. First generate a table combining both large and small tables. Then create a calculate column 'distance' Then group by using summerize function