slicing data by time

[Laurence-]

Hi, I'm aware that slicers don't seem to support below "day" level there's an idea for implementing this here .

I have related tables that hold students attending events (student is mapped to a module, which is mapped to the event), which has a date, start time, end time and other data such as a room etc.

I'm trying to create a slicer to indicate number of people attending events in specific building / rooms. 

I'm able to do this at date level, using a measure which counts the number of students based on building -

NumStudents = CALCULATE(COUNTROWS('uor_pbi-stumodules'),ALLSELECTED('uor_pbi-stumodules'),VALUES('uor_pbi-stumodules'[StudentId]))

my data looks something like the below with this - 

Start Date	Start Time	End Time	Building	NumStudents
16/11/2020 00:00:00	10:00	12:00	ABC	27
16/11/2020 00:00:00	10:30	13:00	DEF	30
16/11/2020 00:00:00	10:30	13:00	ABC	12

This is fine, but i'd like to slice the data 'between' time periods also (based on the start time and end time columns for the event).

I want to filter the rows where event starttime <= (min/start time) and event endtime <= (max/end time) 

e.g. if i select say 10:00-13:00 I want to count the number of students in the building at that time (which would include rows where the event has started before 10:00 for example).  

I have created a calculated table to filter the time (hour/minute) with the below, and was thinking to use this somehow to filter my events table?

Time = VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Hour", [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0), (0)), "Second", [Value])

RETURN

ADDCOLUMNS(

CROSSJOIN(HourTable, MinuteTable, SecondsTable),

"Time", TIME([Hour], [Minute], [Second])

)

I have a feeling i need to use some further DAX to accomplish this, at the moment I'm a bit lost with how best to tackle the problem though! would be grateful for any ideas 🙂 

Happy to provide any further information as required! thanks in advance

[Laurence-]

Hi @v-robertq-msft! Thanks for your reply! 🙂 I've been slowly working it out, I don't think that totally did what I wanted, possibly just the way that i'm counting the rows, however i'm getting closer.. Using the following  (from the Time query) I can filter to the hours by setting my slicer to the numeric 'Hour'  column - 

Hour Min Sec Time
0 0 0 00:00:00
0 1 0 00:01:00
0 59 0 00:59:00
1 0 0 01:00:00
1 59 0 01:59:00

Using the "Hour" column for my slicer appears to work fairly well, and i can match to obtain the "time" from my slicer with the following - 

var _min=TIMEVALUE(MINX(ALLSELECTED('Time'),[Time])) 

var _max=TIMEVALUE(MAXX(ALLSELECTED('Time'),[Time]))

The problem with this is that the max value of course is taking minutes into account, so selecting between 06:00 and 15:00 yields: _min - 06:00 _max - 15:59

Do you have any suggestion how i might modify the DAX here to retrieve the higher time value at the start of the hour (i.e. 15:00)

Here's the full function (adapted from yours):

Numtotal = var _min=TIMEVALUE(MINX(ALLSELECTED('Time'),[Time]))
var _max=TIMEVALUE(MAXX(ALLSELECTED('Time'),[Time]))
return
CALCULATE(COUNTROWS('uor_pbi-stumodules'),ALLSELECTED('uor_pbi-stumodules'),VALUES('uor_pbi-stumodules'[StudentId]),FILTER(('Events'),[Start Time]>=_min&&[End Time]<=_max))

Many thanks again!

[v-robertq-msft · Accepted solution]

Hi, @Laurence- 

You can try to use the TIME() function to transform the max value of your time slicer, try a measure like this:

Numtotal = var _min=TIMEVALUE(MINX(ALLSELECTED('Time'),[Time]))
var _max=TIMEVALUE(MAXX(ALLSELECTED('Time'),[Time]))
var _max1=TIME(Hour(_max),0,0)
return
CALCULATE(COUNTROWS('uor_pbi-stumodules'),ALLSELECTED('uor_pbi-stumodules'),VALUES('uor_pbi-stumodules'[StudentId]),FILTER(('Events'),[Start Time]>=_min&&[End Time]<=_max1))

More info about time() function in DAX

Best Regards,

Community Support Team _Robert Qin

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

[Laurence-]

Thanks @v-robertq-msft , you hero 😉 Now I just need to go and learn DAX better myself to have worked this out sooner.

It's possibly a bit of a workaround to use a number series to match for time, but it works quite well for my needs if I just need to worry about hourly intervals!

Changing the end filter also to the following with your reccommendation for the time transformation seems to have done the trick!

FILTER(('Events'),[End Time]>_min&&[Start Time]<_maxHour))

[v-robertq-msft]

Hi, @Laurence- 

Glad to hear that you have learned from this!😁

would you like to mark my reply as a solution so that others can learn from this too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft]

Hi, @Laurence- 

According to your description, I think you can use this measure to get the total of student numbers based on the selection of your Time slicer, like this:

Num total =

var _min=MINX(ALLSELECTED('Time'),[Time])

var _max=MAXX(ALLSELECTED('Time'),[Time])

return

CALCULATE(SUM('Table'[NumStudents]),FILTER(ALL('Table'),[Start Time]<=_min&&[End Time]<=_max ))

Then you can create a Slicer using “List type” and select the max and min time manually because Power BI can’t support “Between” Slicer to display time:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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