cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Himanshu_Tdc Frequent Visitor
Frequent Visitor

DAX: Extract Start Date from one column and End date from another column if flag is consecutively 1

Hi,

 

I have a table as follows:

 

StatusTimestampJob NameFailure Type
Internal Error06-12-2019 07:53EContinuous
Internal Error06-12-2019 07:48EContinuous
Internal Error06-12-2019 07:43EContinuous
Internal Error06-12-2019 07:38EContinuous
Internal Error06-12-2019 07:33EContinuous
Internal Error06-12-2019 07:55DContinuous
Internal Error06-12-2019 07:50DContinuous
Internal Error06-12-2019 07:45DContinuous
Internal Error06-12-2019 07:40DContinuous
Internal Error06-12-2019 07:35DContinuous
Internal Error06-12-2019 07:30DContinuous
Internal Error06-12-2019 04:40EDiscrete
Internal Error06-12-2019 07:55CContinuous
Internal Error06-12-2019 07:50CContinuous
Internal Error06-12-2019 07:45CContinuous
Internal Error06-12-2019 07:40CContinuous
Internal Error06-12-2019 12:40EDiscrete
Internal Error06-12-2019 07:35CContinuous
Internal Error06-12-2019 07:30CContinuous
Internal Error06-12-2019 07:53FContinuous
Internal Error06-12-2019 07:48FContinuous
Internal Error06-12-2019 07:43FContinuous
Internal Error06-12-2019 07:38FContinuous
Internal Error06-12-2019 07:33FContinuous
Internal Error06-12-2019 07:52IContinuous
Internal Error06-12-2019 07:47IContinuous
Internal Error06-12-2019 07:42IContinuous
Internal Error06-12-2019 07:37IContinuous
Internal Error06-12-2019 07:32IContinuous
Internal Error06-12-2019 07:55LContinuous
Internal Error06-12-2019 07:50LContinuous
Internal Error06-12-2019 07:45LContinuous
Internal Error06-12-2019 07:40LContinuous
Internal Error06-12-2019 07:35LContinuous
Internal Error06-12-2019 07:30LContinuous
Internal Error06-12-2019 23:53KContinuous
Internal Error06-12-2019 23:43KContinuous
Internal Error06-12-2019 23:33KContinuous
Internal Error06-12-2019 23:23KContinuous
Internal Error06-12-2019 23:13KContinuous
Internal Error06-12-2019 23:03KContinuous
Internal Error06-12-2019 22:53KContinuous
Internal Error06-12-2019 22:43KContinuous
Internal Error06-12-2019 22:33KContinuous
Internal Error06-12-2019 22:23KContinuous
Internal Error06-12-2019 22:13KContinuous
Internal Error06-12-2019 22:03KContinuous
Internal Error06-12-2019 21:53KContinuous
Internal Error06-12-2019 21:38KContinuous
Internal Error06-12-2019 21:28KContinuous
Internal Error06-12-2019 21:18KContinuous
Internal Error06-12-2019 21:08KContinuous
Internal Error06-12-2019 20:58KContinuous
Internal Error06-12-2019 20:48KContinuous
Internal Error06-12-2019 20:38KContinuous
Internal Error06-12-2019 20:28KContinuous
Internal Error06-12-2019 20:18KContinuous
Internal Error06-12-2019 20:08KContinuous
Internal Error06-12-2019 19:58KContinuous
Internal Error06-12-2019 19:48KContinuous
Internal Error06-12-2019 19:18KContinuous
Internal Error06-12-2019 19:08KContinuous
Internal Error06-12-2019 18:58KContinuous
Internal Error06-12-2019 18:48KContinuous
Internal Error06-12-2019 18:38KContinuous
Internal Error06-12-2019 18:28KContinuous
Internal Error06-12-2019 18:18KContinuous
Internal Error06-12-2019 18:08KContinuous
Internal Error08-12-2019 10:33KContinuous
Internal Error08-12-2019 10:23KContinuous
Internal Error08-12-2019 10:13KContinuous
Internal Error08-12-2019 10:03KContinuous
Internal Error08-12-2019 09:53KContinuous
Internal Error08-12-2019 09:43KContinuous

 

 

I'd like to create a seperate table from this where for every 'Job Name' I have different periods defined with 'Start Date' and 'End Date' extracted from the 'Timestamp' field.
Start Date is the first timestamp for that job when the Failue Type is Continuous, 
End Date is the last timestamp for that job when the Failure Type is Continuous.

This defines a period. And there can be many periods for that job. The table obviously has to be sorted before finding these 'continuous' periods broken by in between 'Discrete' Failure Type.

 

So I need the following table calculated in the end:

Job NameStart DateEnd Date
E06-12-2019 07:3306-12-2019 07:53
D06-12-2019 07:3006-12-2019 07:55
C06-12-2019 07:3006-12-2019 07:55
F06-12-2019 07:3306-12-2019 07:53
I06-12-2019 07:3206-12-2019 07:52
L06-12-2019 07:3006-12-2019 07:55
K06-12-2019 18:0806-12-2019 23:53
K08-12-2019 09:4308-12-2019 10:33

 

 

However I have achieved this in python, I can't seem to be able to write DAX for the same! 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX: Extract Start Date from one column and End date from another column if flag is consecutivel

Hi @Himanshu_Tdc ,

 

I edit the file again, add an index and then create a new column "rank4" to get date.

You could refer to my test file.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

5 REPLIES 5
Super User II
Super User II

Re: DAX: Extract Start Date from one column and End date from another column if flag is consecutivel

Hi @Himanshu_Tdc 

try new table

NewTable = summarize('Table1';Table1[Job Name];"StartDate";min(Table1[Timestamp]);"EndDate";max(Table1[Timestamp]))

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Himanshu_Tdc Frequent Visitor
Frequent Visitor

Re: DAX: Extract Start Date from one column and End date from another column if flag is consecutivel

Hi @az38 ,

 

If only it were that simple!

As I said, between the minTime and maxTime for every job, I have multiple continuous periods defined by the timestamp and Failure Type.

 

Here is a part of table and comments for more clarity describing what I'm saying.

Source Table:

StatusTimestampJob NameFailure TypeComments
Internal Error06-12-2019 23:53KContinuousPeriod 2 Ends
Internal Error06-12-2019 23:43KContinuous|
Internal Error06-12-2019 23:33KContinuous|
Internal Error06-12-2019 23:23KContinuous|
Internal Error06-12-2019 23:13KContinuous|
Internal Error06-12-2019 23:03KContinuous|
Internal Error06-12-2019 22:53KContinuous|
Internal Error06-12-2019 22:43KContinuous|
Internal Error06-12-2019 22:33KContinuous|
Internal Error06-12-2019 22:23KContinuous|
Internal Error06-12-2019 22:13KContinuous|
Internal Error06-12-2019 22:03KContinuous|
Internal Error06-12-2019 21:53KContinuous|
Internal Error06-12-2019 21:38KContinuous|
Internal Error06-12-2019 21:28KContinuous|
Internal Error06-12-2019 21:18KContinuous|
Internal Error06-12-2019 21:08KContinuous|
Internal Error06-12-2019 20:58KContinuous|
Internal Error06-12-2019 20:48KContinuous|
Internal Error06-12-2019 20:38KContinuous|
Internal Error06-12-2019 20:28KContinuous|
Internal Error06-12-2019 20:18KContinuous|
Internal Error06-12-2019 20:08KContinuous|
Internal Error06-12-2019 19:58KContinuous|
Internal Error06-12-2019 19:48KContinuous|
Internal Error06-12-2019 19:18KContinuous|
Internal Error06-12-2019 19:08KContinuous|
Internal Error06-12-2019 18:58KContinuous|
Internal Error06-12-2019 18:48KContinuous|
Internal Error06-12-2019 18:38KContinuous|
Internal Error06-12-2019 18:28KContinuous|
Internal Error06-12-2019 18:18KContinuous|
Internal Error06-12-2019 18:08KContinuousPeriod 2 Starts
Internal Error06-12-2019 11:18KDiscrete|
Internal Error06-12-2019 11:08KDiscrete|
Internal Error08-12-2019 10:33KContinuousPeriod 1 Ends
Internal Error08-12-2019 10:23KContinuous|
Internal Error08-12-2019 10:13KContinuous|
Internal Error08-12-2019 10:03KContinuous|
Internal Error08-12-2019 09:53KContinuous|
Internal Error08-12-2019 09:43KContinuousPeriod 1 Starts

 

 

Output Table expected:

Job NameStart DateEnd Date
K06-12-2019 18:0806-12-2019 23:53
K08-12-2019 09:4308-12-2019 10:33

 

 

I hope it's clearer.

Community Support Team
Community Support Team

Re: DAX: Extract Start Date from one column and End date from another column if flag is consecutivel

Hi @Himanshu_Tdc ,

 

You could create a rank column to set groups and get the value.

Then use MIN() and MAX() functions to get start date nad end date.

Here is the result visual, I used a column "BasedDate" to group up the table.

1-1.PNG

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Himanshu_Tdc Frequent Visitor
Frequent Visitor

Re: DAX: Extract Start Date from one column and End date from another column if flag is consecutivel

Hi @v-eachen-msft 

 

It works for now, but I guess it won't work if I have multiple continuous periods for the same job in same day! (So what if both periods were on 6th Dec for job K?)
In the sample data I didn't inlcude that case, but it can happen in the future. In that case grouping records on day and taking the minimum of timestamp in that day also won't work, right?

 

 

Community Support Team
Community Support Team

Re: DAX: Extract Start Date from one column and End date from another column if flag is consecutivel

Hi @Himanshu_Tdc ,

 

I edit the file again, add an index and then create a new column "rank4" to get date.

You could refer to my test file.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors