Forum Discussion

davehardikkumar's avatar
davehardikkumar
Helper I
3 years ago
Solved

Slice by Hour

Hello,

 

I have live conneciton and I want to fiter the data by hour range like I want to filter after 4.30 pm. I have date and time in one column.

How can I create 24 hour filter which would slice the data? I have tried to use relative time but it give me whole hour.

 

Please guide me in the direction.

 

Thank you!

  • ValtteriN's avatar
    ValtteriN
    3 years ago

    Hi,

    Here is one way to do this:

    Data:

     

    Dax:

    Filter after 4.30 =
    var _selectedvalue = MAX('Table (30)'[Column1])
    var _hour = HOUR(_selectedvalue)
    var _min = MINUTE(_selectedvalue)
    return

    SWITCH(TRUE(),
    _hour>=17,1, //check for after 17  
    _hour>=16 && _min >= 30, 1, // check for 16:30-17
    0) //if not these then 0

    End result and explanation:

     

    Place the measure as a filter like in the picture. Afterthis you can apply relative date filter of 1 day. Since the measure will only allow values after 16.30 (4.30 pm) the result should be like you described.

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

7 Replies

  • P_d2023's avatar
    P_d2023
    Regular Visitor
    3 years ago

    Hi, you can split the hour from the date/column in the data view.

     

    if you create a custom column and use the below dax expression. you can then use the hour in a seperate filter.

     

    this will round the hour back so 20:59 would show as 20

     

     

     

    • davehardikkumar's avatar
      davehardikkumar
      Helper I
      3 years ago

      as I am using live connection, I cant create column or table. do you have any other way?

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    3 years ago

    Hi,

    Here is one way to do this:

    Data:

     

    Dax:

    Filter after 4.30 =
    var _selectedvalue = MAX('Table (30)'[Column1])
    var _hour = HOUR(_selectedvalue)
    var _min = MINUTE(_selectedvalue)
    return

    SWITCH(TRUE(),
    _hour>=17,1, //check for after 17  
    _hour>=16 && _min >= 30, 1, // check for 16:30-17
    0) //if not these then 0

    End result and explanation:

     

    Place the measure as a filter like in the picture. Afterthis you can apply relative date filter of 1 day. Since the measure will only allow values after 16.30 (4.30 pm) the result should be like you described.

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
    • davehardikkumar's avatar
      davehardikkumar
      Helper I
      3 years ago

      Thanks for your help but I want user to pick any hournot exac 4.30 and then filter the data. so how can I achieve that?

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        3 years ago

        In that case you can use parameters. e.g.


        Filter after 4.30 dynamic =
        var _selectedvalue = MAX('Table (30)'[Column1])
        var _hour = HOUR(_selectedvalue)
        var _min = MINUTE(_selectedvalue)
        return

        SWITCH(TRUE(),
        _hour>=[Hour Value] && _min >= [Minute Value], 1,
        0)

        Result:

         

        So insert parameters from here:

         

        For hours 0 to 23 and for minutes 0 to 59. Increment 1