I can't seem to figure out how to have a time of day slicer that lets me use the between style slice and/or a relative time based on minutes/hours. The slicer converts my time of day numbers into dates even though the column type is set to time - 12/30/1899 to be exact! Anyone else figured out why it does this or if any other store tools allow granular filtering based on time of day (not just by hour?)
Solved! Go to Solution.
You can probably achieve this through the use of a couple of disconnected tables. One for range and the other for time.
If you give me a bit more detail on how you would like to slice your data then post here and I'll try and mock up a simple model for you.
Proud to be a Datanaut!
I have a separate time and separate date column. I tried creating a new combined column hoping that would help, but the slicer visual only shows a date. I want to look at specific times like 8:30AM-9:30AM for specific records entered.
It is not at all clear to me how this answer solves the original request. Apologies! Even after reading the referenced discussion I remain mystified.
Here is what I need: a way to be able to select either a single time value or a range of time values from a TimeOfDay table, using a slicer with a slider.
For example, I have separate Date and TimeOfDay tables. The TimeOfDay table has 86,400 rows, one for each second of the day. It also has columns for Hour and Minute (just integer indexes).
The desired behavior is to allow the user to select either a single date or a range of dates in a Date slicer with slider, then select either a single time value or a range of time values in a a Time slicer with slider, and see the data filtered appropriately in other visuals. By separating Date and TimeOfDay, in-day patterns might be easily distinguished in the data within a range of Dates.
Even Hour granularity might be acceptable, but being able to dynamically switch among Hour, Minute, and Second would be ideal. Ask for the moon, you might get it :-).
As the OP notes, when a TimeOfDay value (represented as data type time(7) in SQL Server and of data type Time in Power BI) is shown in a Date slicer, the only thing shown textually in the slicer is the "beginning of time" date December 30, 1899. This makes perfect sense, but is not useful in my scenario.
Will continue to research this question and will post any useful results as they are found. Can anyone point me to a resource that can help me deliver the desired behavior? Thanks in advance.
@Sam, Phil, thanks guys. It's not clear to me how Sam's referenced discussion answers the op's question. After reading said discussion I remain mystified. Apologies!
Here's what I need: a way to allow the user to select either a single TimeOfDay at Hour and/or Minute and/or Second granularity, or a range of TimeOfDay, either before or after selecting a range of Dates at Date granularity. The goal is to allow focusing on data for an arbitrary continuous range of Times within a Date, across a range of Dates. I hope that makes sense.
Separating Date and Time and allowing the user to select each as needed can be a powerfull analytical tool. We need it. Our data are timestamped to the second.
Currently when a Time (SQL time(7), Power BI Time) is put in a Date slicer, the only value textually displayed is the "beginning of time" value 12/30/1899. This makes perfect sense, but is not useful in our scenario.
Will continue to research how to meet this requirement, and will post any useful results here. Can anyone point me to helpful resources? Thanks in advance.
@MikeDonnellan Hi Mike, similiar to you I am currently working on data where I want to filter out by a set date period, followed by time of day as an adjustable scrolling splicer (where the data type of the hours are still 'time'). Were you able to resolve your issue in a different way to the above solution? Thanks!
You may try this PBIX which contains a table with date and time values and some randomly generated numbers in Power Query and wherein the data time data columns are used in a slicer.
Has anyone found a solution to this issue? I also want to narrow my search using a slicer between two dates at specific time periods. ex. 1-Jul-2018 6:30am to 10-Jul-2018 6:30am
Any help would be amazing. This has me perplexed!