Countrows from the same data between time intervals

metalfortune — Tue, 03 Sep 2024 23:57:22 GMT

Hi every body,
Im trying to use measures with DAX to obtain the max number of travels(which are my rows) within 2 hours in my dataset that can also be interpreted as max busy vehicles
what i can think so far is the next:

Occupation = MAXX(

GROUPBY(data,[Time],"Traffic",SUMX(

CURRENTGROUP(),-(-(ALL(data[Time])<=[Time] && ALL(data[Time])>[Time]-1/12))

)

),[Traffic])

However, the code is not working.

Thank you for advice!

Here a sample of my data:

Time

01/01/2024 12:25:00 AM

01/01/2024 12:50:00 AM

01/01/2024 03:00:00 AM

01/01/2024 05:45:00 AM

01/01/2024 06:00:00 AM

01/01/2024 09:00:00 AM

01/01/2024 12:00:00 PM

01/01/2024 12:40:00 PM

01/01/2024 02:00:00 PM

01/01/2024 03:30:00 PM

01/01/2024 07:00:00 PM

02/01/2024 12:50:00 AM

02/01/2024 01:00:00 AM

02/01/2024 06:20:00 AM

02/01/2024 08:50:00 AM

02/01/2024 12:25:00 PM

02/01/2024 06:15:00 PM

02/01/2024 10:00:00 PM

02/01/2024 11:00:00 PM

02/01/2024 11:30:00 PM

03/01/2024 12:25:00 AM

03/01/2024 12:50:00 AM

03/01/2024 01:00:00 AM

03/01/2024 06:20:00 AM

03/01/2024 08:45:00 AM

03/01/2024 10:30:00 AM

03/01/2024 06:15:00 PM

03/01/2024 10:30:00 PM

03/01/2024 11:00:00 PM

Re: Countrows from the same data between time intervals

ahadkarimi — Tue, 03 Sep 2024 19:26:26 GMT

Hi metalfortune, give this a try, and if you encounter any issues, let me know.

Create a calculated column:

TimeBucket = INT(HOUR([Time])/2) + INT(MINUTE([Time])/120) + DATEVALUE([Time])

Then, create a measure:

MaxBusyVehicles = CALCULATE( MAXX( SUMMARIZE( data, [TimeBucket], "VehicleCount", COUNTROWS(data) ), [VehicleCount] ) )

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

Re: Countrows from the same data between time intervals

metalfortune — Tue, 03 Sep 2024 23:55:22 GMT

somehow bumped with what I wanted (I believe cant understand my own solution at all).

Here is the solution to the groupby but something weird is making my max here 26 instead of 25, i believe has to be with rounding 1/12 (the 2 hours) spoiling the the > and turning similar to >=,

then I have my full solution:

Occupation = MAXX(

ADDCOLUMNS( GROUPBY(data,[Time]),

"Busy",COUNTROWS(FILTER(data,data[Time]<=EARLIER([Time]) && data[Time]>EARLIER([Time])-1/12)))

,[Busy])
Compared to yours, individual times look the same but final numbers perform different:

Anyway, thanks for being my supplier ahadkarimi, take care.

topic Re: Countrows from the same data between time intervals in DAX Commands and Tips

Countrows from the same data between time intervals

Re: Countrows from the same data between time intervals

Re: Countrows from the same data between time intervals