Forum Discussion
Advanced filter by Hour column request me date. Why?
Hi
I have a column with hour date format.
When I'm trying to apply a filter from hour to hour with advanced filter always request me date when the column doesn't have date!
Any help? Thanks
This happens because in Power BI there is no such thing as a pure 'Hour' data type.
When you use Advanced filtering, Power BI treats the column as a full Date/DateTime value. Even if your visual only shows 13:00, the real stored value is '1899-12-30 13:00:00' as in pic below, so the advanced filter still uses a DateTime.
Instead of using 'HoursOnly' (example on my above picture), create a new Column,
HourNumber = HOUR('Table'[DateTimeValue]) and use Column in your filter.
2 Replies
This happens because in Power BI there is no such thing as a pure 'Hour' data type.
When you use Advanced filtering, Power BI treats the column as a full Date/DateTime value. Even if your visual only shows 13:00, the real stored value is '1899-12-30 13:00:00' as in pic below, so the advanced filter still uses a DateTime.
Instead of using 'HoursOnly' (example on my above picture), create a new Column,
HourNumber = HOUR('Table'[DateTimeValue]) and use Column in your filter.Hi absolut33,
that's an interesting and somewhat surprising behaviour. Thank you for sharing. I didn't know that and have no idea how to apply an advanced filter to a column formatted as time value directly.
Are you interested in a workaround? Or would you like to report this as an issue? Or both?
Please let me know.
