Forum Discussion
Smarter DateTime drill down aggregation?
Hi all,
The default drill down behavior in PowerBI is that when using drill down for lets say from year -> day (this can be via quarter and month), it provides me with the the data per day of the month (first below). What I would like to drill down to however is the aggregation per day for each day in the total dataset (see 2nd image below). Thus providing me with an increasingly finer timeline with each level I drill down from the top level (can be from years->days but also for example from days -> seconds).
So I understand that I could somewhat achieve this behavior with building a custom date table that merges columns such as YrQtrMonthDay; and then built a custom hierarchy to this. But this is quite laborous and gets into a nightmare when drilling down into the time scale (a custom datetable would then get in the hundredths of millions of rows just for a few years).
So, what is the best way to achieve the drill down behavior as in the second image (especcially when also drilling down into the time scale; e.g. from weeks/days to hours or seconds)?
Hope you can provide me with a neath method for this, thanks in advance!
Format of the data:
| DateTime | Data (randNr0-100) |
| 19-10-2019 15:00 | 23 |
| 19-10-2019 16:00 | 65 |
| ... | |
| 08-06-2021 07:00 | 2 |
PowerBI behavior:
Desired behavior:
HI G3N3XT,
So you mean to drill through the records into time level? If that is the case, drill your records to the detail level(second) of time values and it will expand the records and obviously affect the performance.
In my opinion, I'd like to suggest you create a stand-alone timetable that accurately to the seconds level.
Then you can build the visuals that summary on date levels and design a report page with time level records that link to the raw table with 'drill through' features and filter on the date filter.Set up drill through in Power BI reports
Regards,Xiaoxin Sheng
2 Replies
you would need to create the time fields and then stack the fields on top of each other, so assign a value to each level of granularity as the year quarter month date is. It really depends on what type of analysis you are trying to do. Usually its best practise to separate the date and time fields and do analysis on them separately. if you supply some data in text form that would be useful to show you.
HI G3N3XT,
So you mean to drill through the records into time level? If that is the case, drill your records to the detail level(second) of time values and it will expand the records and obviously affect the performance.
In my opinion, I'd like to suggest you create a stand-alone timetable that accurately to the seconds level.
Then you can build the visuals that summary on date levels and design a report page with time level records that link to the raw table with 'drill through' features and filter on the date filter.Set up drill through in Power BI reports
Regards,Xiaoxin Sheng
