Forum Discussion
slicing data by time
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.
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!
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.
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))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.