Forum Discussion

davehardikkumar's avatar
davehardikkumar
Icon for Helper I rankHelper 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 slic...
  • 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/
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity 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
Icon for Helper I rankHelper 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
    Icon for Community Champion rankCommunity 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