Forum Discussion
Continuous X-Axis with Custom Date Hierarchy
Hi,
I am working with a dataset that has timestamps every 15 minutes. I need to create a simple line chart that can drill down on the actual timestamp and then roll up to daily, monthly, and yearly levels. I was able to create a custom date hierarchy that had all these levels, but it is fixed on a categorical axis. This creates a scroll bar and does not auto-size the axis, which is not functional with several months of data.
When using the default date hierarchy (year, quarter, month, day), it seems you are able to create a continuous axis, but this fails to support my need to drill deeper than a day. Additionally, I found that you can remove the hierarchy alltogether and select the timestamp field only, but this does not allow me to roll up to daily, monthly, yearly levels.
Is there a workaround for creating a custom hierarchy with a continous or auto-sizing X-axis? I have searched all over and can't seem to find a solution.
Link to another post that has a similar question with no helpful answer: https://community.powerbi.com/t5/Desktop/Continuous-hierarchy-axis/m-p/395530#M180446
Hi Anonymous ，
I have a new idea: create four columns:Year ,Quarter, Month,Day,using the following dax expressions:
Year = YEAR('Table'[TimeStamp])Quarter = QUARTER('Table'[TimeStamp])Month = MONTH('Table'[TimeStamp])Day = DAY('Table'[TimeStamp])Then create a line chart without using date hierarchy,and put all the columns in X-axis:
And you will see:
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
5 Replies
- Greg_Deckler
Community Champion
Can you provide a sample/example representation of your data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Sure thing, I'm not able to attach the file but I have replicated the table structure below. I am trying to chart the "Sample Data" column over time.
TimeStamp Sample Data 3/2/20 11:00 PM 273 3/2/20 11:15 PM 275 3/2/20 11:30 PM 236 3/2/20 11:45 PM 298 3/3/20 12:00 AM 285 3/3/20 12:15 AM 216 3/3/20 12:30 AM 211 3/3/20 12:45 AM 200 3/3/20 1:00 AM 289 3/3/20 1:15 AM 286 3/3/20 1:30 AM 287 3/3/20 1:45 AM 221 3/3/20 2:00 AM 265
v-kelly-msftCommunity Support
Hi Anonymous ，
I suggest you use drillthrough funtion:
Remove the hierarchy alltogether and select the timestamp field only,then create 4 columns to calculate out the related day,month,quarter and year,such as below:
Finally create a drillthrough page,there create 4 line charts,showing the data in days,months,quarters and years.
Here is the reference about drillthrough.
If you need,pls click here to get my .pbix file.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!v-kelly-msft sorry for the delayed response. This drillthrough workaround is not going to work for me because I need to have several visuals on a single page and having to drillthrough on each of them is going to create a large amount of extra visuals. If there was a way to do the reverse of your suggestion and have a date hierarchy with a drillthrough into the actual timestamp, that may work as there is only one extra visual, but I do not believe this is possible. I am really hoping Power BI will add a continuous X-Axis when using a custom date hierarchy.
- v-kelly-msft
Hi Anonymous ，
I have a new idea: create four columns:Year ,Quarter, Month,Day,using the following dax expressions:
Year = YEAR('Table'[TimeStamp])Quarter = QUARTER('Table'[TimeStamp])Month = MONTH('Table'[TimeStamp])Day = DAY('Table'[TimeStamp])Then create a line chart without using date hierarchy,and put all the columns in X-axis:
And you will see:
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
