How to use the 'between' option on a slicer for a datatime field and be able to specify 'TIME'

[AaronToth]

Hi,

We are able to use the 'between' option on a slicer for fields that are of type "date".  It seems for fields that are of 'datetime' you can't use the between option.  Is this correct?  If so, is there a work around?

The second part.  How do you get the "To" and "From" textboxes to show the 'time' portion of the field in the data?  Right now it seems like both boxes will always only show the date.

[v-eqin-msft · Accepted solution]

Hi @AaronToth ,

To my knowledge, built-in slicer doesn't support time-level filtering(except List mode).

An idea similar with what you expect has been submitted in the following link, please vote it up and you can add comments.Hopes Power Bi will implement this in nearby future—— Time Slicer

My workaround is to create a Time table that contains all timestamp every 30 seconds, as shown below:

Time = ADDCOLUMNS( GENERATESERIES(0,60*60*24-30,30) , "Time", 
var _hour=TRUNC([Value]/60/60)
var _min=TRUNC(([Value]- _hour*60*60)/60)
var _sec=[Value] -_hour*60*60-_min*60
return CONVERT(DATE(2022,3,17) &" "& TIME(_hour,_min,_sec),DATETIME))

Then createa visuals, and apply two different level filters:

1. Page-filter

2. For your visual-filter, please create a flag measure, apply it to filter pane, set as "is 1":

Measure = 
var _seconds=HOUR(MAX('Table'[DateTime])) *3600 + MINUTE(MAX('Table'[DateTime]))*60+SECOND(MAX('Table'[DateTime]))
return IF(_seconds>=MIN('Time'[Value]) && _seconds<=MAX('Time'[Value]),1,0)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-eqin-msft · Accepted solution]

Hi @AaronToth ,

To my knowledge, built-in slicer doesn't support time-level filtering(except List mode).

An idea similar with what you expect has been submitted in the following link, please vote it up and you can add comments.Hopes Power Bi will implement this in nearby future—— Time Slicer

My workaround is to create a Time table that contains all timestamp every 30 seconds, as shown below:

Time = ADDCOLUMNS( GENERATESERIES(0,60*60*24-30,30) , "Time", 
var _hour=TRUNC([Value]/60/60)
var _min=TRUNC(([Value]- _hour*60*60)/60)
var _sec=[Value] -_hour*60*60-_min*60
return CONVERT(DATE(2022,3,17) &" "& TIME(_hour,_min,_sec),DATETIME))

Then createa visuals, and apply two different level filters:

1. Page-filter

2. For your visual-filter, please create a flag measure, apply it to filter pane, set as "is 1":

Measure = 
var _seconds=HOUR(MAX('Table'[DateTime])) *3600 + MINUTE(MAX('Table'[DateTime]))*60+SECOND(MAX('Table'[DateTime]))
return IF(_seconds>=MIN('Time'[Value]) && _seconds<=MAX('Time'[Value]),1,0)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AaronToth · Accepted solution]

Here is the final version!  Now the user can use the "Between" option for a slicer that holds a mapped 'value' to DateTime.  So, the slicer the user is using won't show the dates, but the 2 cards below will.  This is as good as we can do for now.  See image.

Thanks to @v-eqin-msft for getting me started with the idea!

[AaronToth]

@v-eqin-msft - After opening the .pbix I see a bit more and actually see some potential.  I created another slicer to hold the "value" column.  This slicer is set to "Between".  The issue is that the values the users see are number likes 30600, 30605, 30610...etc.  What I can do is this.  

Since those numbers above 30600, 30605, 30610...etc = a date.time.  I can create two more measures that get the MIN and MAX values from the slicer.  Once I have those two values, I can display them in cards.  But the best part is, that I'll display their corresponding Date.Time instead of Value.

If this works, the user will user a slicer (between) seeing those 'VALUES' but will also see the Date.Time in another CARD.  I'll keep you posted, up vote and accept the solution.  Thanks again @v-eqin-msft 

[AaronToth]

@v-eqin-msft  I don't see how the user gets to select "Time" in their filter selection.  Am I missing something?  I will open the .pbix and check it out for further understanding.

[AaronToth]

How do I get this slicer to show the 'time' portion in the 2 boxes

The field looks like this in the data

[mh2587]

You want the slicer in the screenshot or something else

[AaronToth]

I know how to extract the time portion from the DateTime field.  With this method, I am able to add a second slicer that just holds the values 0-23 (for hour).  This is good, but we are not accomplishing that use-case.  With this method, if you selected
03/25/2022 - 03/26/2022 (for the date slicer)
7 - 13 (for the hour slicer)
That would return rows between and including those dates and between and including those hours.
So you would have two chunks of data (6 hours x 2 days)

We are trying to do a range like this --->
03/23/2022 8:30AM to 03/24/2022 2:30PM
When you do it like this, you are doing it with one chunk of data that spans over two days.  This is very different than that above.

[AaronToth]

I want to see the slicer in a screenshot (but the two boxes have to include the TIME portion)

[mh2587]

Click on three dots in the right top corner then select between

[AaronToth]

I have done everything you have suggested - it is not working and I don't think we're still talking about the same thing.  Can you reply to my picture post where I say how I would like to get the visual to look.  Can you get the visual to look the way I am asking?  

[AaronToth]

If I do these steps

-add slicer to report

-add datetime field to report

-change slicer to 'slider' = enabled

--Slicer shows two boxes for "TO" and "From"

--2 boxes both show just dates

-add datetime field to 'page filters'

-change to advanced filtering

--UI wants a date in the from and a date in the to.

--If I do this, the slicer on the report doesn't do what I am saying.  

I will post a screenshot to better explain this.

[AaronToth]

If I do the steps

-add this datetime field to page filters

-select advanced filtering

-select 'is on or after' AND 'is on or before'

-- the filtering UI wants me to put a date in both boxes.

This is what I am saying.  The user needs to select the before/after DATE + TIME from the report slicer.

[AaronToth]

When I enabled "Show visual headers", I then see the option for "Between".  However, when the values are presented in the 2 between textboxes, the TIME portion is stripped away.  I'm basically looking for the ability for a person to specify something like ->

#Between
03/24/2022 8:00AM - 03/24/2022 11:45AM
this is how the data is stored in this field. 

However, the slicer is showing these as the options
03/24/2022 - 03/24/2022

Thanks again!

[AaronToth]

I don't see how this will work.  If I use the filters on this visual, or filters on this page, then I specify the range for them.  I want the user to be able to use the slicer where they can pick BETWEEN two datetime values.  

Does your solution do this?  Thanks for the quick reply!

[mh2587]

Yes You can get through advance option from page filter on Date column by selecting is on after and is on before then apply the filter

[AaronToth]

it expects me to put dates in those boxes too.  How do we dynamically set the end date?  As in, tomorrow the dates will grow.  Do I have to go back to this page filter and extend the dates?

[mh2587]

Use is on after and is on before from page filter