Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Relative Reference

I am trying to write a formula for PowerQuery that looks at a cell value and if it equals a specified value retuns the value of a cell that is in the next row and over one column. I want this value t...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Since i could not get the ref to work i ended up doing the following

     

    Adding two Index columns one starting with 0 the other with 1

     

    Doing a merge table with itself and refrencing the two index columns. this shifted the time values to the same line as the Action and then it became a simple if statment without the ref. 

     

    Thanks all for the ideas& help on this one! 

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Frunkis,

 

You can use measure instead, try DAX below and check if it can meet your requirement:

 

Result =
VAR previous_time =
    CALCULATE (
        MAX ( ReportA06272018[Total Agents Needing Help] ),
        FILTER (
            ALL ( ReportA06272018 ),
            ReportA06272018[Index.1]
                = MAX ( ReportA06272018[Index.1] ) - 1
        )
    )
VAR previous_state =
    CALCULATE (
        MAX ( ReportA06272018[Action] ),
        FILTER (
            ALL ( ReportA06272018 ),
            ReportA06272018[Index.1]
                = MAX ( ReportA06272018[Index.1] ) - 1
        )
    )
RETURN
    IF (
        previous_state = "Release (Not Ready)",
        MAX ( ReportA06272018[Total Agents Needing Help] ) - previous_time
    )

Regards,

Jimmy Tao