cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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])``````
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors