Slicer based on date and hour

[momihail]

I have two tables, Notebooks and Pens.

I am trying to display 2 individual area charts, one for # notebooks per hour and one for number of pens per hour, as well as 2 scorecards which will show the total of all the pens and notebooks respectively for the time interval selected in a common slicer which manages both tables.

I tried building a common date time table:

DateTimeCalendar =
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( DATE ( 2019, 3, 1 ), DATE ( 2019, 12, 31 ) ),
UNION (
ROW ( "Time", TIME ( 1, 0, 0 ) ),
ROW ( "Time", TIME ( 2, 0, 0 ) ),
ROW ( "Time", TIME ( 3, 0, 0 ) ),
ROW ( "Time", TIME ( 4, 0, 0 ) ),
ROW ( "Time", TIME ( 5, 0, 0 ) ),
ROW ( "Time", TIME ( 6, 0, 0 ) ),
ROW ( "Time", TIME ( 7, 0, 0 ) ),
ROW ( "Time", TIME ( 9, 0, 0 ) ),
ROW ( "Time", TIME ( 10, 0, 0 ) ),
ROW ( "Time", TIME ( 11, 0, 0 ) ),
ROW ( "Time", TIME ( 12, 0, 0 ) ),
ROW ( "Time", TIME ( 13, 0, 0 ) ),
ROW ( "Time", TIME ( 14, 0, 0 ) ),
ROW ( "Time", TIME ( 15, 0, 0 ) ),
ROW ( "Time", TIME ( 16, 0, 0 ) ),
ROW ( "Time", TIME ( 17, 0, 0 ) ),
ROW ( "Time", TIME ( 18, 0, 0 ) ),
ROW ( "Time", TIME ( 19, 0, 0 ) ),
ROW ( "Time", TIME ( 20, 0, 0 ) ),
ROW ( "Time", TIME ( 21, 0, 0 ) ),
ROW ( "Time", TIME ( 22, 0, 0 ) ),
ROW ( "Time", TIME ( 23, 0, 0 ) ),
ROW ( "Time", TIME ( 24, 0, 0 ) )
)
),
"DateTime", [Date] + [Time],
"Hour", HOUR ( [Time] )
)

And creating a relationship between the two other tables’s DateTime and this table’s Date. However, that doesn’t create a relationship to the hours as well – which means that my slider will not be able to filter based on hour intervals as well (this means that while my area chart is able to view all data among hours, the score cards which keep a sum of counts for that particular time interval do not have anything displayed). Is there any workaround for this? I have over 6 tables in the page that need to be unified by the slicer...Thanks so much!

[jsh121988]

I think you went about it correctly. The only reason why the realtionship wouldn't be working is if the Pen and notebook datetimes aren't aggregated to the hour. Meaning 2019-03-19 04:53:26 needs to be 2019-03-19 04:00:00. You have this in your example, but i wanted to be sure.

The default slicer doesn't support the hourly level, but i think there are custom slicers in the store that accomplish this.

A workaround would be creating a new calc column on each table that is a type of Number (preffered) or Text, with the formula being MyDateYMDH = FORMAT([MyDate], "yyyyMMddhh"), then relate them. However, this only solves the relationship problem, not the slicer problem.

[momihail]

Thanks so much for reply! Which slicer would be more appropriate from the store? The slicer i am using cannot select hours so scorecards display (Blank). I have tried others but was not able to find one that works.

[jsh121988]

Looks like Time Brush Slicer could do it, but the selection is limited 😞 .

[momihail]

Yes, time brush was not a good option for me... 😞