Forum Discussion

thmonte's avatar
thmonte
Icon for Helper IV rankHelper IV
8 years ago
Solved

Use value from other row in dataset based on condition

I am trying to do some advanced DAX/PowerQuery equations and I am coming up short and looking for some help.   I have a dataset like the below and the use case is to get an average amount of time s...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi thmonte

     

    Sorry, I missed that.  I have ammeded my calc

     

    Column = 
VAR x = MAXX(
            FILTER(
                'Table1',
                'Table1'[equipID] = EARLIER('Table1'[equipID]) 
                && 'Table1'[timestamp] > EARLIER('Table1'[timestamp])
                && 'Table1'[down] = FALSE()
                ),
                'Table1'[timestamp])

VAR y = if(ISBLANK(x),NOW(),x)

RETURN IF( 'Table1'[code]<>"",
        DATEDIFF('Table1'[timestamp],y,MINUTE))
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi thmonte

 

Sorry, I missed that.  I have ammeded my calc

 

Column = 
VAR x = MAXX(
            FILTER(
                'Table1',
                'Table1'[equipID] = EARLIER('Table1'[equipID]) 
                && 'Table1'[timestamp] > EARLIER('Table1'[timestamp])
                && 'Table1'[down] = FALSE()
                ),
                'Table1'[timestamp])

VAR y = if(ISBLANK(x),NOW(),x)

RETURN IF( 'Table1'[code]<>"",
        DATEDIFF('Table1'[timestamp],y,MINUTE))
thmonte's avatar
thmonte
Icon for Helper IV rankHelper IV
8 years ago

Phil_Seamark

 

Thank you so much for the fast response.  I'll have to study the DAX that you used so I can come up with my own one day.

 

Will respond and close when I test against the real data.