Find the value in a column for the row where another column is closest to a third col in other table

[MRD_JR]

I have three tables, C, I, and O, with C and I each having a relationship to O on column P.

Table C has a bunch of measurements of F and Po for several P.

Table I has several measurements of R for several P

How do I find C[Po] for the row that has the closest C[F] to the average of I[R]?

I want to displaying this in a Matrix with rows O[P] and values including AVERAGE(I[R]), MAX(C[Po]), C[F] at MAX(C[Po])

For C[F] at MAX(C[Po]) I am using this measure:

FAtMaxPo = CALCULATE (
    selectedvalue ( C[F]),
    FILTER ( ALL ( C[Po] ), C[Po] = MAX ( C[Po] ) )
)

but when I try to extend it to pull the comparison value from another table I get a blank result:

PoAtR = 
CALCULATE (
    SELECTEDVALUE ( C[Po] ),
    FILTER ( ALL ( C[F] ), ABS(C[F] - averagex(RELATEDTABLE('I'), [R] )) < 20 )
)

Edit: Trying to clarify based on the question guide

Relationships:

O		C
P	1:*	P

O		I
P	1:*	P

Sample Data:

O:

P	...
K1
K2
S1

C:

P	F	Po	...
K1	119.95	0.407
K1	118.45	0.199
K1	114.50	0.240
K2	117.51	0.201
K2	111.05	0.243

I:

P	R	...
K1	117.08
K1	119.78
K2	111.04
K2	110.98

Desired result:

P	avg(R)	max(Po)	F at max(Po)	Po at avg(R)
K1	118.43	0.407	119.95	0.199
K2	111.01	0.243	111.05	0.243

[v-shex-msft · Accepted solution]

Hi @MRD_JR,

You can consider creating a variable table in measure formula to add a new column with diff value based on F and avg(f), then you can find out the min diff and use it to look up correspond Po values:

Po at avg(R) =
VAR _currP =
    SELECTEDVALUE ( O[P] )
VAR _avg =
    CALCULATE ( AVERAGE ( I[R] ), FILTER ( ALLSELECTED ( I ), [P] = _currP ) )
VAR temp =
    ADDCOLUMNS ( FILTER ( ALLSELECTED ( C ), [P] = _currP ), "Diff", [F] - _avg )
RETURN
    MINX ( FILTER ( temp, [Diff] = MINX ( temp, [Diff] ) ), [Po] )

Regards,

Xiaoxin Sheng

[MRD_JR]

Thanks, that works with a couple adjustments for my specific situation. I ended up using this:

PoAtR = 
var _curP = SELECTEDVALUE(O[P])
var _avgR = CALCULATE(average('I'[R]), FILTER(allselected('I'), [P] = _curP))
var temp = addcolumns(filter(allselected(C), [P] = _curP), "Diff", abs([F] - _avgR))
return averagex(filter(temp, [Diff] = minx(temp, [Diff])), [Po])

[Greg_Deckler]

Didn't come close to following that. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490