DAX - 15 minute Interval Question

[CB_Radio]

Hi there,

I have a dataset that has a Car ID (Unit ID), the time (and date) the driver logged in and the time (and date) the driver logged out.  I am trying to calculate the number to unique units logged on during each 15 minute block of time each day and display it in a table with the date down the left side and the 15 minute block across the top.  I keep running into memory errors because the data set is huge (millions of rows).  The other issue I have is that I can't figure out how to show which date the 15 minute block occured in if the unit was logged in over night (i.e. logged on at 1900hrs on January 1st and logged out at 0700hrs on January 2nd).  

I have a sample data set I've dumped into an Excel spreadsheet:

https://www.dropbox.com/s/ovt0mtcgov66lk3/Sample%20Data.xlsx?dl=0

Any help would be greatly appreciated.  I tried this solution but this didn't seem to work:

https://community.powerbi.com/t5/Desktop/15-Minute-Increments-between-2-Timestamps/m-p/330330

Thanks,

CB_Radio!

[Anonymous · Accepted solution]

Hi @CB_Radio,

In my opinion, I think you not need to create such huge table to store all expand datetime. You can stored time value part in a table then use below formula to lookup and calculate.

Interval count = 
VAR currStart =
    TIMEVALUE ( SELECTEDVALUE( Data[EVAL_LOGIN_TIME] ) )
VAR currEnd =
    TIMEVALUE ( SELECTEDVALUE ( Data[EVAL_LOGOUT_TIME] ) )
RETURN
    IF (
        currStart <= currEnd,
        COUNTROWS (
            FILTER (
                ALL ( 'Interval List' ),
                [Range Start] >= currStart
                    && [Range End] <= currEnd
            )
        ),
        COUNTROWS (
            FILTER (
                ALL ( 'Interval List' ),
                [Range Start] >= currStart
                    || [Range End] <= currEnd
            )
        )
    )


Interval Detail = 
VAR currStart =
    TIMEVALUE ( SELECTEDVALUE ( Data[EVAL_LOGIN_TIME] ) )
VAR currEnd =
    TIMEVALUE ( SELECTEDVALUE ( Data[EVAL_LOGOUT_TIME] ) )
RETURN
    IF (
        currStart <= currEnd,
        CONCATENATEX (
            FILTER (
                ALL ( 'Interval List' ),
                [Range Start] >= currStart
                    && [Range End] <= currEnd
            ),
            [Rolling Interval],
            ","
        ),
        CONCATENATEX (
            FILTER (
                ALL ( 'Interval List' ),
                [Range Start] >= currStart
                    || [Range End] <= currEnd
            ),
            [Rolling Interval],
            ","
        )
    )

Regards,

Xiaoxin Sheng