Filter by time range- Hour

[AshleyRenee]

I am trying to set a filter to only show data within a certain time, 5AM-9PM. I cannot seem to figure a way to do this, looks like I can only filter by day, month etc... Any help would be appreciated. 

[AlB · Accepted solution]

Hi @AshleyRenee 

1.  You need to create a one-column table (or a new column in the date table you have already) with times only. Have a look at this for more info on how the DateTime type works in DAX. For instance, if you need hours only (00:00, 01:00, 02:00,...) you can build it as follows (this is a calculated table😞

TimeTable =
SELECTCOLUMNS (
    VAR _HoursStep =
        DIVIDE ( 1, 24 )
    RETURN
        GENERATESERIES ( 0, _HoursStep * 23, _HoursStep ),
    "Hour", [Value]
)

Note the SELECTCOLUMNS is only to set the name of the column to "Hours". You can use the table here this new

2. Then you need a column with only time in your fact table. You can create a new calculated column by extracting the time only from your date column. Something like this:

TimeColumn = FactTable[DateColumn] - TRUNC(FactTable[DateColumn])

 3. Finally, you have to create a relationship between the two newly created columns