CODEWITHSUNDEEP
powerbidax
Alexis Olson
Alexis Olson

Reputation: 40224

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

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.

Upvotes: 5

Views: 482

Answers (1)

Joe G
Joe G

Reputation: 1776

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.

Using Measures

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.

Using Columns

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

Upvotes: 2

Related Questions