Ratio of columns in 2 separate tables based on DateTime in each table

[momihail]

I have data in two tables, Apples and Oranges. I want to compute Apple/Orange ratio for each time interval, with caveat that if one of the entries are missing for a certain hour, that hour will be skipped. The date will be up to 1h intervals of precision (fixed hour). This data needs to be filterable by a slicer, which means that i will have to build an intermediary relationship table between the 2 up to hours of precision. I am not sure how to go about accomplishing this and allowing slicer filtering.

The relationship table would be:

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] )
)

But i do not know how to divide between the 2 tables to obtain the intermediary Ratio column.

[v-shex-msft · Accepted solution]

Hi @momihail ,

Nope, current slicer range selection mode not support time type.

If you drag date time fields to slicer, it will only keep date part. You can consider to switch slicer to 'list' mode and show all records.

Regards,

Xiaoxin Sheng

[momihail]

This logic unfortunately doesnt suit me, i will have entries for each day in a year. I will mark as accepted since it gives me closure on the functionality of the slicer