Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hekutoru
Frequent Visitor

Group by day and hour, and get max value from another column

Hi, I  need to group or get a measure with the datetime and time columns from next sample.

 

iddatetimetime
128/03/2023 09:30:03 pm205
228/03/2023 09:28:50 pm 
328/03/2023 09:25:10 pm198
428/03/2023 09:20:34 pm 
528/03/2023 09:11:40 pm123
628/03/2023 09:04:50 pm 
728/03/2023 08:46:23 pm112
828/03/2023 08:33:55 pm95
928/03/2023 08:18:36 pm 
1028/03/2023 07:48:48 pm 
1128/03/2023 07:28:18 pm90

 

The idea is that I need to group by day and hour in 'datetime' column, and get the max value from the 'time' colum, for example for day 28/03 at 9-10pm, i'd need to get the 205, then for 28/03 8-9pm get the 112 value and so. This would also apply when I found the 27/03 day. The idea is to find the max value within the range of 1 hour each hour in a day.

 

The objective is to get something like this:

 

Screen Shot 2023-03-29 at 10.42.03 AM.png

 

To be abel to create a bar chart, each bar representing 1 hour, where I can see what is that maximum value in that hour.

 

I'd really aprecciate all your support.

1 ACCEPTED SOLUTION

@hekutoru Must have gotten here when it was still an image. See attached PBIX below signature. You create a disconnected table like this:

Dates and Hours = 
    SELECTCOLUMNS(
        ADDCOLUMNS( 
            GENERATE( 
                CALENDAR(MIN('Table'[datetime]), MAX('Table'[datetime])), 
                GENERATESERIES(0,23)
            ),
            "datetime", [Date] + [Value]/24
        ),
        "datetime",[datetime]
    )

and then a measure like this:

Measure = 
  VAR __dateTime = MAX('Dates and Hours'[datetime])
  VAR __date = DATE(YEAR(__dateTime), MONTH(__dateTime), DAY(__dateTime))
  VAR __hour = HOUR(__dateTime)
  VAR __table = FILTER(ALL('Table'), DATE(YEAR(__dateTime), MONTH(__dateTime), DAY(__dateTime)) = __date && HOUR('Table'[dateTime]) + 1 = __hour)
  VAR __result = MAXX(__table, [time])
RETURN
  __result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
watkinnc
Super User
Super User

You can add two custom columns, DateTime.Date and Time.Hour, then group by date and hour, and use the Max aggregation for your "time" column.

 

--Nate 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Greg_Deckler
Super User
Super User

@hekutoru Didn't post the sample data as text so having to wing it. Maybe:

Measure =
  VAR __dateTime = MAX('Table'[datetime])
  VAR __date = DATE(__dateTime)
  VAR __hour = HOUR(__dateTime)
  VAR __table = FILTER(ALL('Table'), DATE('Table'[datetime]) = __date && HOUR('Table'[dateTime]) = __hour)
  VAR __result = MAXX(__table, [time])
RETURN
  __result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

thank you, I've already post as text, add some information too

@hekutoru Must have gotten here when it was still an image. See attached PBIX below signature. You create a disconnected table like this:

Dates and Hours = 
    SELECTCOLUMNS(
        ADDCOLUMNS( 
            GENERATE( 
                CALENDAR(MIN('Table'[datetime]), MAX('Table'[datetime])), 
                GENERATESERIES(0,23)
            ),
            "datetime", [Date] + [Value]/24
        ),
        "datetime",[datetime]
    )

and then a measure like this:

Measure = 
  VAR __dateTime = MAX('Dates and Hours'[datetime])
  VAR __date = DATE(YEAR(__dateTime), MONTH(__dateTime), DAY(__dateTime))
  VAR __hour = HOUR(__dateTime)
  VAR __table = FILTER(ALL('Table'), DATE(YEAR(__dateTime), MONTH(__dateTime), DAY(__dateTime)) = __date && HOUR('Table'[dateTime]) + 1 = __hour)
  VAR __result = MAXX(__table, [time])
RETURN
  __result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler thank you very much.
almost working. The problem is that when I have more than one day the value is being duplicated for every hour

For example, the same value for 28/03/2023 9:00:00 pm is being duplicated for 27/03/2023 9:00:00 pm, even if I don't have values for that 27/03 day and time

thank you, I've already post as text, add some information too

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors