I am working on a report in Power BI. One of the tables in my data model collects sensor data. It has the following columns:
- Serial (int) i.e. 123456789
- Timestamp (DateTime) i.e. 12/20/2016 12:04:23 PM
- Reading (decimal) i.e. 123.456
A new record is added every few minutes, with the current reading from the sensor.
Power BI automatically creates a Hierarchy for the DateTime column, which includes Year, Quarter, Month and Day. So, when you add a visual to your report, you can easily drill down to each of those levels.
I would like to include the "Time" part of the data in the hierarchy so that you can drill down one more level after "Day", and see the detailed readings during that period.
I have already set up a Date table, using the CALENDARAUTO() function, added all of the appropriate columns, and related it to my Readings table in order to summarize the data by date - which works great. But it does not include the "Time" dimension.
I have looked at the following SO questions, but they didn't help:
Time-based drilldowns in Power BI powered by Azure Data Warehouse
Creating time factors in PowerBI
I also found this article, but it was confusing:
Power BI Date & Time Dimension Toolkit