Tricky/impossible matrix calculation - no idea if it is even possible in powerpvot

[masplin]

I have 2 tables. The first "Mot Centres" contains a list of UK garages each with an Easting and Northing for their location. The 2nd table is a list of traffic flow on various roads again each with a check point location in Eastings and northings. I want to work out which road check point is nearest to each garage. In my head the calculation is simple, but no idea if DAX can handle it

1. calculate the distance between Garage 1 and all road check points using pythagoras )((Easting MOT-Easting CP)^2)-((Northing MOT-Northing CP)^2))^0.5.
2. Then on the row for the Garage 1 return the minimum value of this. So essentially I need to create a column for each garage of all the distance and then apply a MIN calculation.
3. Then I need to use this min value to somehow filter the CP table to return the CP ID.

Is this just way out of PP depth as the 2 tables are 22,000 row and 26,000 rows so that is a pretty big matrix!!!!!

Thanks for any advice as to whether i'm barking up the wrong tree.

Mike

 

[Ozeroth]

Mike,
Here is one possible way of doing it with a calculated column added to the Mot Centres table. This formula gives the name of the closest checkpoint.
I used a FIRSTNONBLANK/TOPN pattern from SQLBI I'd seen here.
Note, you don't need to bother with the square root if just comparing distances.

I tested it on two tables with 30k rows, and it did take a minute or two to calculate the calculated column (32-bit Excel 2010).
Could be more efficient ways of doing it, possibly using a measure if you don't need all the results at once?

[FONT=courier new]Closest Checkpoint =[/FONT]
[FONT=courier new]FIRSTNONBLANK ([/FONT]
[FONT=courier new]    TOPN ([/FONT]
[FONT=courier new]        1,[/FONT]
[FONT=courier new]        VALUES ( TrafficFlow[Checkpoint name] ),[/FONT]
[FONT=courier new]        CALCULATE ([/FONT]
[FONT=courier new]            ( -1 )[/FONT]
[FONT=courier new]                * ([/FONT]
[FONT=courier new]                    ( [/FONT][FONT=courier new]VALUES ( MotCentres[East] ) - VALUES ( TrafficFlow[East] )[/FONT][FONT=courier new] )[/FONT][FONT=courier new]^2[/FONT]
[FONT=courier new]                    + ([/FONT][FONT=courier new] VALUES ( MotCentres[North] ) - VALUES ( TrafficFlow[North] )[/FONT][FONT=courier new] )[/FONT][FONT=courier new]^2[/FONT]
[FONT=courier new]                )[/FONT]
[FONT=courier new]        )[/FONT]
[FONT=courier new]    ),[/FONT]
[FONT=courier new]    1[/FONT]
[FONT=courier new])[/FONT]

 

[masplin]

Genius!!!!

For my education I was wondering if you can explain:

1. Can you use the same idea to give you the 2nd closest? TOPN seems to return the Top so many so 222 would give me the top 2 not the 2nd?
2. I first wrote your formula without wrapping the field names in VALUES and it failed. Why do I need VALUES when all the inputs are definitely whole numbers?
3. I tried to change the field being recorded from the CP (which is a number) to the Road Name and got an error says table of multiple values. Seems odd as thought this calculation was calculating all the distances then creating a table of 1 row for the largest (smallest distance) and then giving me the VALUE in the first expression. Each CP only appears once in the traffic flow table. It can't be that 2 CP are exactly the same distance from one of the garages as the first calculation would error. Also when you navigate to error seems to be all records not one in particular. Same if I try to return any other field form Traffic flow.

Really appreciate you help

Mike

 

[Ozeroth]

1. The below expression seems to work for "2nd closest checkpoint". There may well be more elegant ways of doing this!
It creates a table with two columns [Checkpoint name] and [Distance rank] using ADDCOLUMNS.
It then filters [Distance rank] to be <=2 (this could be changed to any desired rank). The reason this is not "=2" is to allow for ties where two or more garages are equally close to the garage and are all ranked 1. It also filters out the [Closest checkpoint] which again is only relevant in the case of a tie for closest. It then takes the top [Checkpoint name] based on [Distance rank] using TOPN (which could be multiple rows if there are ties) and uses FIRSTNONBLANK to reduce this to a single [Checkpoint name].

=FIRSTNONBLANK (
    SUMMARIZE (
        TOPN (
            1,
            FILTER (
                ADDCOLUMNS (
                    VALUES ( TrafficFlow[Checkpoint name] ),
                    "Distance rank", RANKX (
                        VALUES ( TrafficFlow[Checkpoint name] ),
                        CALCULATE (
                            ( -1 ) * (  ( VALUES ( MotCentres[East] ) - VALUES ( TrafficFlow[East] )  )^2  + ( VALUES ( MotCentres[North] ) - VALUES ( TrafficFlow[North] )  ) ^ 2 )
                        )
                    )
                ),
                [Distance rank] <= 2
                    && TrafficFlow[Checkpoint name] <> [Closest Checkpoint]
            ),
            [Distance rank]
        ),
        TrafficFlow[Checkpoint name]
    ),
    1
)

I uploaded a sample Excel 2010 file here (with two versions of the new calculated column):
https://www.dropbox.com/s/y3rmhjlo7qo832u/Distance3.xlsx?dl=1

2. In this case, VALUES is needed to convert a naked column name to a one-row table which can be used as a scalar. This works since we this calculation is taking place in the context of a single row of the Mot Centres table, and CALCULATE converts the row context within TOPN (i.e. the rows of VALUES(TrafficFlow[Checkpoint name]) ) to filter context.

3. I'm guessing this is because there are multiple rows with the same road name in the TrafficFlow table? The VALUES(...) functions above only work as intended if there is a single row from each table.

 

[masplin]

wow brilliant. Thanks so much. going to spend some time trying to understand the syntax.