Solved: Countrows from the same data between time interval...

metalfortune · ‎09-03-2024

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

metalfortune · ‎09-03-2024

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.

View solution in original post

ahadkarimi · ‎09-03-2024

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!

metalfortune · ‎09-03-2024

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.

Countrows from the same data between time intervals

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025

Join us at FabCon Vienna from September 15-18, 2025

Countrows from the same data between time intervals

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025