Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
rpinxt
Power Participant
Power Participant

Getting a rounded Time value on my column chart X-axis

This seems to be rather complicated.....

A picture will probably explain best:

rpinxt_0-1683101037600.png

 

So my source has a timestamp. The refresh schedule is every half hour but the timestamp it returns is the end time of the run.

Therefore is not always a neat full or half hour but 1 or 2 minutes later.

 

As you see I can simply make a measure which rounds it down to the corresponding full hour/half hour.

But I cannot use this measure in my colum chart!!

 

Certainly there must be something I can do to not have 9:31 and 10:01 but 9:30 and 10:00 on my x-axis??

 

ps: This is direct query so a custom column on the data table is not possible.

6 REPLIES 6
Muhammad110
Advocate I
Advocate I

Since you mentioned that this is not possible in your case due to direct query, another option is to use the "Floor" function in your chart's category axis expression. This will allow you to round the timestamps down to the nearest full or half hour directly in the chart.

Here's an example of how you could use the Floor function in your chart expression:

 

=FLOOR('Your Timestamp Column', "00:30:00")

This expression will round the timestamps down to the nearest half hour. If you want to round to the nearest full hour instead, you can use "01:00:00" instead of "00:30:00".

Yes I can make a measure like that and the measure works.

However you cannot use this measure as an X-Axis in a chart...

You're right, measures can't be used directly as the X-axis in a chart. However, there is a workaround you can use to achieve the desired result.

Instead of using the measure as the X-axis, you can create a calculated column in your data model that rounds the timestamp down to the nearest half hour or full hour. Then, use this calculated column as the X-axis in your chart.

Here's an example of how you could create a calculated column that rounds the timestamp down to the nearest half hour:

Rounded Timestamp =TIME(HOUR('Your Timestamp Column'),FLOOR(MINUTE('Your Timestamp Column') / 30, 1) * 30,0) let me know if this works as this is the only solution I am thinking of right now

True....but in direct query mode you cannot make calculate columns 😉

 

rpinxt_0-1683118953937.png

 

Ahh just forgot while giving you the solution sorry 

rpinxt
Power Participant
Power Participant

Ok apparently this cannot be done, made peace with that.

 

So I asked for the timestamp to reflect the starting time of the source scheduled run instead of the completion time.

Could be done he said, so that would then be the only solution if it cannot be done in Power BI.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.