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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
airportplanner
Frequent Visitor

Time Chart - Display Time in Y-axis

Hello, I have this dataset:

Capture.PNG

 

 

 

 

an

 

 

And I am trying to create this in Power BI:

 

Capture.PNG

 

The excel chart is using the Time Elapsed since OBL to first/last button columns (last 2 columns on the table).

 

My issue is that I can not illustrate time values on the y axis. I can only reproduce the chart by converting time into numbers with decimals.

 

Can you please advice if there is a way to do this?

 

Thank you very much in advance

1 ACCEPTED SOLUTION

Hi @airportplanner,

 

As of now, there is no way.

 

Prateek Raina

View solution in original post

9 REPLIES 9
phersan
Regular Visitor

I recently got stuck with this same issue, and found a somewhat suitable solution by converting a timestamp to an integer (Ex: 9:24:38 AM would equal 92438).

 

You can reference that original solution here by @v-shex-msft on another thread:

https://community.fabric.microsoft.com/t5/Desktop/Display-duration-as-HH-MM-SS-on-Y-axis/m-p/88111/h...

 

However, I just wanted to share an update that Power BI rolled out a Dynamic formatting for DAX measures feature, which gets us even closer, by displaying an integer as HH:MM:SS.

 

Basically, just follow shex's instructions to create a DAX measure that represents your time as an integer (they used MAX, but I needed an AVERAGE) :

 

 

DAX Start Time = (HOUR(AVERAGE(data_table[Start Time]))*10000) + (MINUTE(AVERAGE(data_table[Start Time]))*100) + (SECOND(AVERAGE(data_table[Start Time]))*1)

 

 

Next, you'll want to change the format of your measure to "Dynamic", and you'll get a new drop-down option to change the format of your number. I chose "00:00:00" as my format (you can include negative and zero formats as well, if you want to be thorough)

phersan_0-1697735736453.png

 

Next, create a Line Graph using your new DAX measure as the Y-Axis. You'll need to format the Y-Axis in the settings of the chart so that the Display Units don't get auto-formatted to thousands (K). Set that option to "None"

phersan_1-1697731437771.png

 

Here is what my final Line Graph looks like on my test app:

phersan_2-1697731650429.png

 

The only drawback I have noticed so far is that you won't be able to format your time in 12HH format with an AM/PM indicator. I played around with the formatting strings by making my AM times negative and PM times positive in my measure, and including the AM and PM indicators in the format string, but still no luck. Got it to work as a table, but the graph is wrong when it tries to plot negative and positive integers together. I don't believe you can use date/time format patterns directly with the Dynamic format feature (i.e. just typing "HH:MM:SS" as your format string didn't work for me). But perhaps someone smarter than me can come up with a system to hack it together with a more complex measure or manipulating separate columns or something.

 

Also, it doesn't look like I can attach a sample pbix file, but if it eventually lets me then I will include what I put together as my test.

 

Hope this helps!

Paulo

sammeuel
Regular Visitor

Sitck with your original solution (convert to number) and add a tooltip which displays date value was best I could come up with. 

RKM
Helper IV
Helper IV

How (or Why!) is this marked as solved? Shouldn't this remain open for MS to revert with some option? Just wondering!

faramar
Regular Visitor

hi

did you found a solution for your issue

No, unfortunately, Power BI does not support this function.

 

You can not have hh:mm:ss on Y axis

prateekraina
Memorable Member
Memorable Member

Hi @airportplanner,

 

There is no Power BI visual which directly supports this.

You can however check out this thread and may find a solution based on R based Chart.

 

Also, kindly vote up this idea.

 

Prateek Raina

@prateekrainaunfortunately the R based chart does not work for me.

 

So are we saying that there is literally no way that a chart can contain time values at the y-axis in Power Bi?

Anonymous
Not applicable

I am in the same boat. Did you able to resolve this issue ?

 

Thanks,

Shubham

Hi @airportplanner,

 

As of now, there is no way.

 

Prateek Raina

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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