Slider Slicer that Includes Time (Not just Date)

[mattkocak]

Hey all,

I have a Time column that I would like to create a slider slicer for, similar to what's available for a Date column.

However, I haven't been able to find a way to do this. If I add a Time column to a slicer and set the visualization method to "Between", it shows a 1899-12-30 date without any other options. As an FYI, all of my data is within a 24-hour period so duplicate times on different dates are not an issue.

Similarly, if I do this with a Date/time column instead of just a Time column, it simply uses the date piece of the value and leaves off the time piece.

I know that one can add a "zoom slider" to some individual visuals to create a similar effect, but I need to actually slice the data based on time, not just adjust the visual display.

Is there a way to implement a slider slicer based on the time piece?

Best,

Matt

[lbendlin · Accepted solution]

No, that is not possible. Use the filter pane instead.

[raymcgaw · Accepted solution]

I was able to make a time slicer, but it was indepentent of the date, so you would first have to filter the date and then the time.

In power query, I split the time and minutes from the timedate into text, then converted back into a number (the minutes need to be padded with a zero):

Number.From(
Text.From(Time.Hour([Timestamp_Time])) &
Text.PadStart(Text.From(Time.Minute([Timestamp_Time])), 2, "0" ))

I created a slicer with the number in it (used Greater than or equal to, but Between also works):

To get it to look like time, I formatted the number to 0#:##

[karpagam]

can you please elaborate the process in step by step in Power BI ?

[Batillo_22]

Hi raymcgaw, thanks for the sweet solution, but how did you format the slicer? In powerBi I can't find the settings you posted for formatting the new number. 

[raymcgaw]

Select the field in the data pane and then go to Column Tools, the format option will be in the main ribbon:

[Amphagory]

First off, thanks for posting your solution.

In implementing your solution, the time slicer show "times" like 01:80.  Were you able to bound in some way what values the number slicer values could show for "minutes" i.e. 00-59?

[raymcgaw]

Just to note, this is just a mask for a number and the way the slicer works is by giving you all numbers in a range.  While 01:80 is not a valid time, it is still filtering the correct range since there will be no time (number) in your dataset greater than 0159 until you get to 0200.  I have done some workarounds below until there is a proper solution:

If you are able to limit your slicer within a fixed hour period (ex. 10:00 - 10:59), you can force the values by adjusting the filters of the number.

If you are slicing times greater than a one hour block (ex. 10:00 to 13:00), then I would suggest removing the slider part of the slicer and just have manual inputs (make sure users know it is 24-hour formatted).  

[mattkocak]

Cool, a very clever solution!

[lbendlin · Accepted solution]

No, that is not possible. Use the filter pane instead.