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
andrewconnell
New Member

Displaying time in line graph

My data source is a collection of elapsed race times over multiple dates. My goal is to display a line graph showing the progress of these elapsed times over the dates they occurred. I understand the Y axis can only have numerical values, so my existing "MM:SS.ms" strings won't work in the graph. I can fix this by converting them to seconds.

 

This fixes the problem & gives me what I want, except for one point: now I'm looking at seconds when my users need to see the time as MM:SS.ms.

 

Ultimately what would be ideal is to tell the line graph "use seconds when plotting everything, but in the display, only use the "MM:SS.ms" in the Y axis and data label while the dates are on the X axis."

 

Does that make sense? Is this possible or are there other ways to achieve the same thing?

1 ACCEPTED SOLUTION

Hi,

 

You can create time column like DAX below, set the Time(seconds) "Sum" ( because it have been set show value as "No calculation" by default, so it won't be aggregated in fact ).

 

Time = FORMAT(SUBSTITUTE(Table1[Time(minutes)],".",":"),"H:mm:ss")

1.png2.png

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

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

View solution in original post

5 REPLIES 5
v-xicai
Community Support
Community Support

Hi @andrewconnell ,

 

As you said, could you please clarify what the red line part is referred to ?

 

4.png

 

I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Amy

It's not a "red line" I'm looking for... it's showing times over time... referencing the image below, you can see the table of data & the graph that does NOT show what I want... some points:

 

  • The values in the graph currently shown is from the green box, but it wants to sum / count / etc... I want the Y axis to show the actual values, not aggregate them
  • The date on the X axis should be the actual dates... not comparing year over year or month over month
  • Ideally, the values I want to show up in the Y axis and on the data labels are from the purple box (Time (minutes)) but I understand that as a text field, I can't graph it... not sure if there's a way to say "use this property to create the chart, but use the values from this field for the Y axis.

Does this help?

 

2019-06-05_08-32-49.png

Hi,

 

You can create time column like DAX below, set the Time(seconds) "Sum" ( because it have been set show value as "No calculation" by default, so it won't be aggregated in fact ).

 

Time = FORMAT(SUBSTITUTE(Table1[Time(minutes)],".",":"),"H:mm:ss")

1.png2.png

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

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

Much better... thanks! However, I'm having one challenge with this: the X axis isn't sorted correctly. I want the dates to be continuous, not based on the values. Notice from the screenshot below the dates aren't in ascending order... that's what I need to see... the most recent date on the far right, the oldest date on the far left... the highlight shows just an example of how the dates are out of order, but it's really the whole chart:

 

2019-06-17_10-26-59.png

Hi,

 

You can use "Sort by" to sort X axis , then choose "Sort ascending".

 

1.png

 

Best Regards,

Amy

 

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

 

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.