Drill down from date to datetime

[BeautifulDash]

I have a dataset called df showing solar power generation/electricity consumption, whereby each variable (load, use, injection, ..) has a float value (e.g.: 1.5, for 1.5 kW), recorded every five minutes so using datetime (e.g: 14/4/2024 13:22:53). A CALENDERAUTO generated Date table allows for filtering dates using a slicer.

When looking at a single day on a column chart, it looks great. When looking at multiple days, or more, there’s too many data points. I want to then aggregate the values by day (or month, or year).

Is there any way to see datetime (without hierarchy) when looking at a single day but date hierarchy, so clustered per day, month, or year, when looking at more than one day, in a single graph?

My values are already measures (e.g.: inject = SUMX(FILTER(df, df[variable] = "inject"), df[value]). The time intelligence/chart adds, in effect, a grouping, so, what I am really asking is how to auto-adjust, or through user interaction (like a slicer); change the scale of the X axis?

Tried:

• When using a date hierarchy, drilling down is possible, but not lower than 1 day, meaning I cannot see the datetime values of day 14/4/2024, for instance. There’s also a way to do this with buttons, I’m aware.
• When using a custom date hierarchy (by generating columns for day, month, year, and time), I can, but then the datetime is not continuous but categorical, which does not allow me to really see my usage visually.

Dashboard (but with Python-generated random data):

To be clear, because in this thread this question was ignored; the X axis needs to be continuous, not categorical, also when drilling down to the lowest level, which is not working out for me.

[BeautifulDash]

That's a good solution, but I cannot get it working. 'Drill-through' from Date hierarchy to Datetime (no hierarchy) does not appear. 

[Anonymous]

Hi @BeautifulDash 

Yes, you should use the date column with out hierarchy, and change the type of the x-axis into Categorical:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeautifulDash]

Yes, that works. I then have to create a custom date hierarchy, sort of, by using MONTH(date) and YEAR(date) as calculated columns, and adding date (so day), month, and year, to the categorical visual to be able to drill down. Odd how many steps and workarounds are needed to simply drill down date and datetime, when the relation between those datapoints should be obvious to the software. Thank you for your help -- this is solved! 

[Anonymous · Accepted solution]

Hi @BeautifulDash 

Please consider using DrillThrough.

While you've mentioned that drilling down to datetime granularity is not directly possible with the date hierarchy, consider using the drillthrough feature in Power BI to create a detailed page that focuses on a single day's data. Users can then select a specific day from the main report page and drill through to the detailed page to see data at the datetime level. This method provides a user-friendly way to navigate from aggregated data to detailed data.

Here I create a set of sample:

Then add a calculated column:

Date = DATE(YEAR('Table'[DateTime]),MONTH('Table'[DateTime]),DAY('Table'[DateTime]))

And change the type of the column into date (yyyy-mm-dd)

Then add a clustered column chart with Date and Value field:

Then in page 2, add a clustered column chart with Datetime and Value field:

Then drag the Date field into the drillthrough bin, you can right-click the clustered column and select the drill through > page 2 to achieve similar operations.

Here for your reference:

Set up drillthrough in Power BI reports - Power BI | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.