Use value from other row in dataset based on condition

[thmonte]

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 spent for each 'code'.  To come up with that I need to take each true value timestamp and comare it against the next false vale timestamp.  Ideally I'd like to store the DATEDIFF in a new column on each TRUE value.  Does anyone know how I can achieve this?  Keep in mind, each equipment ID can have many true/false values.

Thank you in advance!

equipID	down	code	timestamp
25DC-001	FALSE		1/5/2018 8:00AM
25DC-001	TRUE	DN03	1/4/2018 8:00AM
25DC-001	TRUE	DN02	1/3/2018 8:00AM
25DC-001	TRUE	DN01	1/2/2018 8:00AM
25DC-001	FALSE		1/1/2018 8:00AM
25DC-002	FALSE		1/6/2018 8:00AM
25DC-002	TRUE	DN01	1/3/2018 8:00AM
25DC-002	FALSE		1/1/2018 8:00AM

Example: Output

equipID	down	code	timestamp	dateDiff
25DC-001	FALSE		1/5/2018 8:00AM
25DC-001	TRUE	DN03	1/4/2018 8:00AM	1440
25DC-001	TRUE	DN02	1/3/2018 8:00AM	2880
25DC-001	TRUE	DN01	1/2/2018 8:00AM	4320
25DC-001	FALSE		1/1/2018 8:00AM
25DC-002	FALSE		1/6/2018 8:00AM
25DC-002	TRUE	DN01	1/3/2018 8:00AM	4320
25DC-002	FALSE		1/1/2018 8:00AM

[Phil_Seamark]

Hi @thmonte

Any chance you can post the ideal output for that sample of data?  It might help answer a few questions

[thmonte]

Sure @Phil_Seamark

For each True do DATEDIFF to the NEXT False timestamp in group, if no false found use NOW().

DN03 = DATEDIFF(1/4/2018 8:00AM,1/5/2018 8:00AM,MINUTES)

Example: Output

equipID	down	code	timestamp	dateDiff
25DC-001	FALSE		1/5/2018 8:00AM
25DC-001	TRUE	DN03	1/4/2018 8:00AM	1440
25DC-001	TRUE	DN02	1/3/2018 8:00AM	2880
25DC-001	TRUE	DN01	1/2/2018 8:00AM	4320
25DC-001	FALSE		1/1/2018 8:00AM
25DC-002	FALSE		1/6/2018 8:00AM
25DC-002	TRUE	DN01	1/3/2018 8:00AM	4320
25DC-002	FALSE		1/1/2018 8:00AM

[Ashish_Mathur]

Hi @thmonte,

This calculated column formula works as well

=if(Table1[down]=FALSE(),BLANK(),(CALCULATE(MIN(Table1[timestamp]),FILTER(Table1,Table1[equipID]=EARLIER(Table1[equipID])&&Table1[timestamp]>EARLIER(Table1[timestamp])&&Table1[down]=FALSE())))-Table1[timestamp])*1440

Hope this helps.

[Phil_Seamark]

Hi @thmonte

Please try this calculated column

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

RETURN IF( 'Table1'[code]<>"",
        DATEDIFF('Table1'[timestamp],x,MINUTE))

[thmonte]

@Phil_Seamark

This looks to be working beautifully!  Will have to test with the full data set.

Do you have anything if the latest row record for that ID is True to use NOW() and return DATEDIFF?

Example: Last value for 25DC-001 is DN03 with a timestamp of 1/12/2018 8:00AM.  Normally we compare against the next FALSE but in this scenario there isn't one.  Can we use NOW() and the DATEDIFF off that?

Right now with the DAX you provided its taking the DATEDIFF from the last FALSE value which is 1/15/2018 8:00 AM

25DC-001	TRUE	DN03	1/12/2018 8:00:00 AM
25DC-001	FALSE		1/15/2018 8:00:00 AM
25DC-001	TRUE	DN02	1/9/2018 8:00:00 AM
25DC-001	FALSE		1/9/2018 8:00:00 AM
25DC-001	TRUE	DN01	1/5/2018 8:00:00 AM
25DC-001	FALSE		1/5/2018 8:00:00 AM
25DC-001	TRUE	DN03	1/4/2018 8:00:00 AM
25DC-001	TRUE	DN02	1/3/2018 8:00:00 AM
25DC-001	TRUE	DN01	1/2/2018 8:00:00 AM
25DC-001	FALSE		1/1/2018 8:00:00 AM
25DC-002	FALSE		1/6/2018 8:00:00 AM
25DC-002	TRUE	DN01	1/3/2018 8:00:00 AM
25DC-002	FALSE		1/1/2018 8:00:00 AM

[Phil_Seamark · Accepted solution]

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]

@Phil_Seamark

Worked great!  Only thing I had to change was

MAXX to MINX

[Phil_Seamark]

Hi @thmonte

Oh yeah, I saw that afterwards.  Well done! 🙂

[thmonte]

@Phil_Seamark

Thanks!  Maybe you can help here as well

https://community.powerbi.com/t5/Desktop/Create-Visual-Table-based-on-searching-unique-IDs-and-other...

[thmonte]

@Phil_Seamark

Worked great!  Only thing I had to change was

MAXX to MINX

[thmonte]

@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.