Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
MRD_JR
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
P1:*P
O I
P1:*P

 

Sample Data:

O:

P...
K1 
K2 
S1 

C:

PFPo...
K1119.950.407 
K1118.450.199 
K1114.500.240 
K2117.510.201 
K2111.050.243 

I:

PR...
K1117.08 
K1119.78 
K2111.04 
K2110.98 

 

Desired result:

Pavg(R)max(Po)F at max(Po)Po at avg(R)
K1118.430.407119.950.199
K2111.010.243111.050.243
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
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.

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.