Forum Discussion
Slicer for selecting the range between two times
I'm currently trying to put together a slicer that selects a range of times from a 24 hour period, but I"m running into some issues with formatting because there's no built in compatibility in slicers for finding a range of time values, just dates or numerical values. I've managed to get it formatted to work with a military time representation of the time data, but the slider doesn't follow the 60 minutes = one hour formatting, and users would definitely prefer to not have to type in military time manually. Is there anyway to do logical conditions using a pick and choose slicer so I can do something like 'display everything between the two selected time values as a range', or did Microsoft just not leave us any way of getting around this?
Hi J-G
It cannot directly set the time slicer by between, but you can refer to the following soution to implement the similar function.
1.Create three tables.
StartTimeTable = VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0,23), "Hour", [Value]) VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value]) VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value]) return CROSSJOIN(HourTable, MinuteTable, SecondsTable)EndTimeTable = VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0,23), "Hour", [Value]) VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value]) VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value]) return CROSSJOIN(HourTable, MinuteTable, SecondsTable)TimeTable = VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0,23), "Hour", [Value]) VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value]) VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value]) return SUMMARIZE(ADDCOLUMNS( CROSSJOIN(HourTable, MinuteTable, SecondsTable), "Time", TIME([Hour], [Minute], [Second]) ),[Time])2.Create 7 slicers, put the [hour][minue][second] fields of starttime table to the first three slicers,[hour][minue][second] fields of endtime table to the lase three slicers, then put the time field field to the last slicer. set these slicers type to 'dropdown'
3.Create a measure, and put it to the time slicer visual filter.
Measure = var start_time=TIME(Min('StartTimeTable'[Hour]),Min('StartTimeTable'[Minute]),MIN('StartTimeTable'[Second])) var end_time=TIME(Max('EndTimeTable'[Hour]),Max('EndTimeTable'[Minute]),Max('EndTimeTable'[Second])) return IF(SELECTEDVALUE('TimeTable'[Time])>=start_time&&SELECTEDVALUE('TimeTable'[Time])<=end_time,1,0)Then you can select hours minutes seconds above, then the time slicer will display the related time between the times you have chosen.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Thank you!
Hi J-G
It cannot directly set the time slicer by between, but you can refer to the following soution to implement the similar function.
1.Create three tables.
StartTimeTable = VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0,23), "Hour", [Value]) VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value]) VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value]) return CROSSJOIN(HourTable, MinuteTable, SecondsTable)EndTimeTable = VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0,23), "Hour", [Value]) VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value]) VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value]) return CROSSJOIN(HourTable, MinuteTable, SecondsTable)TimeTable = VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0,23), "Hour", [Value]) VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value]) VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value]) return SUMMARIZE(ADDCOLUMNS( CROSSJOIN(HourTable, MinuteTable, SecondsTable), "Time", TIME([Hour], [Minute], [Second]) ),[Time])2.Create 7 slicers, put the [hour][minue][second] fields of starttime table to the first three slicers,[hour][minue][second] fields of endtime table to the lase three slicers, then put the time field field to the last slicer. set these slicers type to 'dropdown'
3.Create a measure, and put it to the time slicer visual filter.
Measure = var start_time=TIME(Min('StartTimeTable'[Hour]),Min('StartTimeTable'[Minute]),MIN('StartTimeTable'[Second])) var end_time=TIME(Max('EndTimeTable'[Hour]),Max('EndTimeTable'[Minute]),Max('EndTimeTable'[Second])) return IF(SELECTEDVALUE('TimeTable'[Time])>=start_time&&SELECTEDVALUE('TimeTable'[Time])<=end_time,1,0)Then you can select hours minutes seconds above, then the time slicer will display the related time between the times you have chosen.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
