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 unit...
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

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