Forum Discussion

Laurence-'s avatar
Laurence-
Frequent Visitor
5 years ago
Solved

slicing data by time

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 modu...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

v-robertq-msft's avatar
v-robertq-msft
Community Support
5 years ago

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-'s avatar
Laurence-
Frequent Visitor
5 years ago

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's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.