How to include 'Time' in Date Hierarchy in Power BI

Question

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

Any ideas?

Thanks!

Answer 1

• We have to separate date & time tables. Also, we don’t need to put the time into the date table, because the time is repeated every day.

• Time dimension is the same principle as a Date dimension, except instead of a row for every day, you would have a row for every minute or every second (depending on how exactly you want to be - I wouldn't recommend including second unless you absolutely needed it, as it greatly increases the number of rows you need - impacting performance). There would be no reference to the date in the time table.

• We include a time/text column since Power BI has service for adding a date from 1899 to time data types. You can add other columns if they'd be helpful to you too.

• We split your DateTime column into a separate date & time columns in fact table, so that you can join the date to the date table & the time to the time table. The time need to be converted to the nearest round minute or second so that every time in your data corresponds to a row in your time table.

• It's worth keeping but hiding the original DateTime field in your data in case you later want to calculate durations that span days.

• In Power BI, we can add the time attribute (or the hour (and minute) attribute) under the month/day attributes on your axis to make a column chart that can be drilled from year => quarter=> month=> day=> hour=> minute. Power BI doesn't care that the attributes come from different tables.

If u want to learn the tool in depth, then come to us and sign up for this Power BI Certification course online at Intellipaat. 

Comments

Your 4th point ..."We split your DateTime column into a separate date & time columns in fact table, so that you can join the date to the date table & the time to the time table. The time need to be converted to the nearest round minute or second so that every time in your data corresponds to a row in your time table."

I am a very beginner in BI - how I can do that, could you explain that, please?
I have an import connection to a SQL Server and that creates a table which includes a field "dateTime".For excample "Mon Jul 08 2019 23:12:07". I want to separate the date and the time to use it for drillthrough down to the hours and minutes in a chart.

Could you support me on that? Maybe a link for a tutorial would be helpful as well.
Many Thanks
Tom

---

Create columns aginst the date field and drop that into the axis under the year/month/day fields.
They would look something like this:
Hour = TIMEVALUE(Table[datetime_field])
Minute = MINUTE(Table[datetime_field])
Set Axis values as

Year
Quarter
Month
Day
Hour
Minute
Basically, you should be able to build your own custom hierarchy and drag and drop them into a hierarchy
Refer to this link for more clarity:
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill