Forum Discussion
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:
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!
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.
Edit:
You can extract the minutes or seconds from the field to create a number field:
4 Replies
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
Hi Mark,
Thank you for you reply. Until the query editor it looks like your solution works perfectly. See the Substraction column:
The Data Type is Any. If I leave the Data Type as it is, I get this data after exiting the query editor:
If I make a chart I get this:
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.
Edit:
You can extract the minutes or seconds from the field to create a number field: