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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

The issue is that usually I would use calculate for this and filter on Distance = "1100"

However I can't know that value in advance and need the value to be obtained from the row its in. I have just read the section in Rob Collies book on Row Context but it really doesn't delve down into this level of detail.

 

Any ideas?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks for the awesome solution. I have now learnt EARLIEST. Always amazed at DAX seems to have a really deep level of functions for almost any purpose.