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
Anonymous
Not applicable

Overlay variables line chart different datetimes

For a project I want to make a line chart. The lines are displayed next to each other, but they need to be on top of / overlay each other.

 

Data is imported from a SQL database with the following columns: Id, DateTime, Metric, Run, Source, TestId, Value.

 

DateTime: 21/10/2018 16:43:25 - 21/10/2018 22:56:58
Metric: multiple variable names
Run: 1, 2 or 3
Source: A or B
Value: decibel numbers

 

The runs are in sequence, for example:
- Source A, Metric B, Run 1, Values, DateTime 21/10/2018 16:43:25 - 21/10/2018 17:45:17
- Source A, Metric B, Run 2, Values, DateTime 21/10/2018 18:18:21 - 21/10/2018 19:20:12
- Source A, Metric B, Run 3, Values, DateTime 21/10/2018 19:52:51 - 21/10/2018 20:54:42

 

If I plot the example in a line chart I get the following:

line_chart.png

 

 

line_chart_settings.pngline_chart_data.png

 

The data is plotted in three lines next to each other because of the datetime values, see example DateTime data. For the project the three lines must overlay each other and not be next to each other. Is there a way to solve this issue in Power Bi? Thank you!

1 ACCEPTED SOLUTION

Hi Cas,

 

In the graph your all rows.value is a count of the values.

See if you can change that to your wanted summarisation in your graph values.

If that isn't possible change the data type in the query editor for the column.

 

sum.PNG

 

Edit:

You can extract the minutes or seconds from the field to create a number field:

substract duration.PNG

 

 

View solution in original post

4 REPLIES 4
mkdejonge
Frequent Visitor

Assuming your data set looks as follows:

 

 

In query editor create a custom key that is unique for the run( source, metric, run)

 

Then we need the find the min date per run, and still be able to see all rows. Therefore we create a group by

 

 

We expand the AllRows column to have all rows including the mindate

 

From that we create a time substraction ( Datetime - mindate)  to have the # minutes after the run has started.

 

 

With the minutes we can overlay all runs and the axis would be like

0 is starting time

1 is minutes after start.

 

This for every run.

 

 

 

Is this the kind of solution you are looking for?

 

Regards,

 

Mark

Anonymous
Not applicable

Hi Mark,

 

Thank you for you reply. Until the query editor it looks like your solution works perfectly. See the Substraction column:

Substraction.jpg

 

The Data Type is Any. If I leave the Data Type as it is, I get this data after exiting the query editor:

Substraction2.jpg

 

If I make a chart I get this:

graph.jpg

 

I think Data Type of Substraction should be Time instead of Any, but if I change it to Time I get an error. Do you know how to solve this or if I do something wrong?

 

Thank you and regards,

Cas

Hi Cas,

 

In the graph your all rows.value is a count of the values.

See if you can change that to your wanted summarisation in your graph values.

If that isn't possible change the data type in the query editor for the column.

 

sum.PNG

 

Edit:

You can extract the minutes or seconds from the field to create a number field:

substract duration.PNG

 

 

Anonymous
Not applicable

Mark, thank you for your help!

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.