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
2 ACCEPTED SOLUTIONS
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#:##
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#:##
can you please elaborate the process in step by step in Power BI ?
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.
Select the field in the data pane and then go to Column Tools, the format option will be in the main ribbon:
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?
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).
