Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Row Context - calculating looking up a value from row

Hi    I cannot fully get row context when doing a calculation. I have created some dummy data to illustrate and hoping to get some help. This is my data, all the values in the far right column fore...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi Anonymous,

     

    We can try to get the values with conditions, then we can test them with "if" statements.

    Result =
VAR level1 =
    CALCULATE (
        MAX ( 'Table1'[56BM] ),
        FILTER (
            'Table1',
            'Table1'[Distance] = EARLIER ( 'Table1'[Distance] )
                && 'Table1'[NameID] = EARLIER ( Table1[NameID] )
                && 'Table1'[Date] < EARLIER ( 'Table1'[Date] )
        )
    )
VAR level2 =
    CALCULATE (
        MAX ( 'Table1'[56BM] ),
        FILTER (
            'Table1',
            'Table1'[NameID] = EARLIER ( 'Table1'[NameID] )
                && 'Table1'[Date] < EARLIER ( 'Table1'[Date] )
                && 'Table1'[Distance]
                    >= EARLIER ( Table1[Distance] ) - 200
                && 'Table1'[Distance]
                    <= EARLIER ( Table1[Distance] ) + 200
        )
    )
RETURN
    IF (
        ISBLANK ( level1 ),
        IF ( ISBLANK ( level2 ), 9999, level2 - ( [weight] - 56 ) / 0.5 ),
        level1
            - ( [weight] - 56 )
            / 0.5
    )

    The columns start with "Test" are to verify  the result only.

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
9 years ago

Anonymous so just so i understand this

 

you want to take the max 56BM where the distance and name are equal and the date is is before the current row date,  and then run this formula Max([56BM])-(([ current row weight]-56)/0.5).  If you are unable to find the exact distance  you want the closest distance, is that higher or lower?  (and this must be within a 400m range on either side?)

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Yes thats correct. The Distance should be no more than a range of 400m. So if we had 1200 anywhere from 1000 to 1400 would be acceptable.