Forum Discussion

CB_Radio's avatar
CB_Radio
New Member
7 years ago
Solved

DAX - 15 minute Interval Question

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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