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

Group By if time between > 1hr

Hi All,

Hope someone will be able to help me

Is it possible to group in DAX categories if the time in between > 1 hr? I need to return count, max time and min time

 

Appreciate any advice

 

Data:

NameDateTime
A05/05/2018 06:42
A05/05/2018 06:43
A05/05/2018 06:45
A05/05/2018 06:51
A05/05/2018 08:43
A05/05/2018 08:46
A05/05/2018 15:20
A05/05/2018 15:22
A05/05/2018 15:23

 

Expected result:

NameCountMinMax
A405/05/2018 06:4205/05/2018 06:51
A205/05/2018 08:4305/05/2018 08:46
A305/05/2018 15:2005/05/2018 15:23
3 REPLIES 3
Super User III
Super User III

Re: Group By if time between > 1hr

Hi @Liwi

 

Try this calculated table

From the Modelling Tab>>New Table

 

Table =
VAR temp1 =
    ADDCOLUMNS ( Table1, "Time", HOUR ( Table1[DateTime] ) )
VAR temp2 =
    SUMMARIZE (
        temp1,
        [Time],
        Table1[Name],
        "Count", COUNT ( Table1[DateTime] ),
        "Min", MIN ( Table1[DateTime] ),
        "Max", MAX ( Table1[DateTime] )
    )
RETURN
    temp2
Try my new Power BI game Cross the River
Super User III
Super User III

Re: Group By if time between > 1hr

@Liwi

 

Please see attached file

 

Gropby.png

Try my new Power BI game Cross the River
Liwi
Frequent Visitor

Re: Group By if time between > 1hr

Thank you @Zubair_Muhammad

 

However you solution gives me max / min / count for each hour while I need to define the groups for each name where the time difference between lines is more than 1 hour, so the difference of MAX of Group 1 - MIN Group 2 > 1 hour.

Perhaps below ilustrates better. 

Would you know how to achieve that?Capture.PNG

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors