Forum Discussion
Filter by time range- Hour
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.
Hi Anonymous
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
1 Reply
- AlB
Community Champion
Hi Anonymous
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