cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbyrd Regular Visitor
Regular Visitor

Time of day slicer

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?)

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Time of day slicer

@sbyrd,

 

You may add multiple slicers to set time value by Using What if parameters, and then refer to this discussion.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
13 REPLIES 13
Super User
Super User

Re: Time of day slicer

Hi @sbyrd

 

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.

 

Phil


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

sbyrd Regular Visitor
Regular Visitor

Re: Time of day slicer

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.

 

Community Support Team
Community Support Team

Re: Time of day slicer

@sbyrd,

 

You may add multiple slicers to set time value by Using What if parameters, and then refer to this discussion.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MikeDonnellan Frequent Visitor
Frequent Visitor

Re: Time of day slicer

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.

MikeDonnellan Frequent Visitor
Frequent Visitor

Re: Time of day slicer

@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. 

5hae Visitor
Visitor

Re: Time of day slicer

@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!

danextian New Contributor
New Contributor

Re: Time of day slicer

Hi @5hae

 

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.

 

https://drive.google.com/open?id=1muN42p5vxZhRWo70sGzKEF8VgAnCZRli

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
sbyrd Regular Visitor
Regular Visitor

Re: Time of day slicer

I never found quite what I was looking for.  Seems simple to just add an hour/minute date filter to the slicer so you can get as granular as you want/need.

Normeister Frequent Visitor
Frequent Visitor

Re: Time of day 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!