Best way to pull the row that corresponds to a max or min

Question

Suppose I have TableA and TableB that do not have a relationship.

On TableA I create a calculated column that computes a minimal value according to a complex expression involving multiple columns from both tables (e.g. see my answer here).

MinValue = MINX(TableA, [ComplexExpression])

This works fine, but what I'm really looking for is the ID for the row where that minimum occurred.

For the sake of a concrete example, let's look at the following:

TableA:

ID   X     Y
-------------
1   2.1  -1.3
2   3.5   0.5
3   6.5   5.9
4   3.1   4.2
5   2.8  -2.7
6   5.7  -1.5

TableB:

ID   X     Y
-------------
A   2.3   2.7
B   2.8  -4.8
C   4.1   1.1
D  -0.5   0.9
E  -3.7   2.4

I've added a calculated column to TableA that gives the minimal Euclidean distance.

MinDist = MINX(TableB, ROUND(SQRT((TableA[X]-TableB[X])^2 + (TableA[Y]-TableB[Y])^2), 2))

Now, I'd like to find the ID from TableB that corresponds to the nearest point. That is, I want the following table:

ID   X     Y   MinDist  B-ID
-----------------------------
1   2.1  -1.3   3.12     C
2   3.5   0.5   0.85     C
3   6.5   5.9   5.28     A
4   3.1   4.2   1.70     A
5   2.8  -2.7   2.10     B
6   5.7  -1.5   3.05     C

I've gotten it to work with the following

MinDist = CALCULATE(MAX(TableB[ID]),
              FILTER(TableB,
                  ROUND(SQRT((TableA[X]-TableB[X])^2 + (TableA[Y]-TableB[Y])^2), 2)
                  = TableA[MinDist]))

but I'd ideally like a more elegant/efficient method where I don't have to use the same complex expression twice.

Well, if you don't know the answers, who does then, Marco and Alberto? — Prakash Gautam, Jun 11 '18 at 01:47
Related idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18476596-custom-reusable-dax-function — Foxan Ng, Jun 11 '18 at 04:21
Related: https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column — Alexis Olson, Sep 04 '19 at 16:01

score 2 · Answer 1 · answered Apr 12 '19 at 19:08

Depending on how you are using your data, this could give you what you need.

Basically, create a measure that acts as a saved function and then call that measure from other measure/column formulas.

So first, I created a measure to calculate the minimum distance.

MinDistToB = 
VAR SelectedX = SELECTEDVALUE(TableA[X])
VAR SelectedY = SELECTEDVALUE(TableA[Y])
RETURN
    MINX(TableB, ROUND(SQRT((SelectedX - [X]) ^ 2 + (SelectedY - [Y]) ^ 2), 2))

Then I created a measure to get the ID.

MinDistToB_ID = 
VAR MinDist = [MinDistToB]
RETURN
    MAXX(FILTER(TableB, [MinDistToB] = MinDist), [ID])

Throwing it all in a matrix, I get this.

If you need them to be columns, you can define the distance as this.

Col_MinDist = [MinDistToB]

And the ID column as this.

Col_MinDistID = MAXX(FILTER(TableB, [MinDistToB] = [Col_MinDist]), [ID])

Putting those into a table, I get this.

With all of this, you can edit the one measure and everything else will get updated accordingly (even the columns).

Best way to pull the row that corresponds to a max or min

1 Answers1