Overlay timeseries (difft time res/ranges; 2-column tables) on a line chart using "Secondary y-axis"

[DanCodiga]

Power BI novice just getting started.

My goal here seems to me like it should be simple but I'm finding it's not.

If the answers to my questions are posted elsewhere, or there are sections of the documentation and/or blogs you think I would benefit from reading, please provide links.

I have two 2-column (timestamp, value) data tables. Each has datetimes in the first column and values (one file has temperature the other has oxygen; different units and ranges) in the second column. 

The two datetime columns have different temporal resolutions and time period ranges.

I can plot the two time series individually using separate line charts:

(I adjusted the x-axis range of the top plot to match the one in the lower plot)

Q1: To do the above, I had to deselect the default "Date hierarchy" checkmark from the x-axis field (timestamps). Why?

Q2: (Related to Q1?) I see that Power BI insists that, even with a two-column file for which I just want to do a simple line plot, some form of aggregation is applied (SUM). (And it labels the plot "Sum of ...", though I just want to plot the individual points without an aggregation, for now.) What should I read, in order to better understand why that is the default (and/or how to turn it off, if that's possible)?

So far the data model is just two independent tables: 

Now my main goal is to overlay the two time series on the same single line chart, using “Secondary y-axis”. 

Q3: What is the simplest way to do this? Can it be done without changing the data model? Or must an new relationship be made between the tables, and/or some third calendar table?

When I try naively adding the other variable into the Secondary y-axis field of one of the existing plots, that variable appears on the plot, with a right-hand y-axis, as desired—but only one point appears, the SUM (see Q1 & Q2) of all its values.

I also tried adding the second Timestamp field to the x-axis, which is allowed, but clearly that's not enough. 

[DanCodiga]

I include at bottom two example data files, in case that's helpful.

It sounds like I should be making a calendar table, adding it to my data model, making some sort of connections to it from my existing two tables, and then putting the calendar table in to the x-axis of the line chart. 

Correct?

About the calendar table, is it limited to having times that fall on a regular grid? I would really like to avoid that if at all possible. The resolution of one record is 15 minutes, and the resolution of the other record is about 1 hour but somewhat irregular. I need to be able to plot the data at the actual times collected, rather than aligning them to a new grid (e.g. through aggregation to hourly means). 

@lbendlin , thanks for the response. For A1, in fact it was required. If I accepted the default, the data were aggregated to daily means. What I did was to uncheck "Date heirarchy" and check "Timestamp" (this is the name/header of the column of dates in the file). I believe this is what made it so that the sum calculation was done over each individual timestamp, and the individual values appeared on the plot instead of daily averages.

Example data file one: (Example data file two is down below it)

Timestamp	Temp[degC]
8/23/2023 12:00	22.94
8/23/2023 12:15	23.68
8/23/2023 12:30	23.9
8/23/2023 12:45	24.06
8/23/2023 13:00	24.66
8/23/2023 13:15	25.58
8/23/2023 13:30	25.46
8/23/2023 13:45	25.24
8/23/2023 14:00	24.62
8/23/2023 14:15	23.72
8/23/2023 14:30	20.02
8/23/2023 14:45	13.66
8/23/2023 15:00	13.2
8/23/2023 15:15	13.14
8/23/2023 15:30	13.14
8/23/2023 15:45	13.16
8/23/2023 16:00	13.16
8/23/2023 16:15	13.18
8/23/2023 16:30	13.2
8/23/2023 16:45	13.2
8/23/2023 17:00	13.22
8/23/2023 17:15	13.26
8/23/2023 17:30	13.28
8/23/2023 17:45	13.32
8/23/2023 18:00	13.36
8/23/2023 18:15	13.42
8/23/2023 18:30	13.42
8/23/2023 18:45	13.5
8/23/2023 19:00	13.64
8/23/2023 19:15	13.8
8/23/2023 19:30	13.96
8/23/2023 19:45	14
8/23/2023 20:00	14.1
8/23/2023 20:15	14.14
8/23/2023 20:30	14.26
8/23/2023 20:45	14.42
8/23/2023 21:00	14.62
8/23/2023 21:15	14.68
8/23/2023 21:30	14.68
8/23/2023 21:45	14.68
8/23/2023 22:00	14.7
8/23/2023 22:15	14.7
8/23/2023 22:30	14.72
8/23/2023 22:45	14.72
8/23/2023 23:00	14.74
8/23/2023 23:15	14.78
8/23/2023 23:30	14.82
8/23/2023 23:45	14.54
8/24/2023 0:00	14.44
8/24/2023 0:15	14.44
8/24/2023 0:30	14.12
8/24/2023 0:45	13.64
8/24/2023 1:00	13.34
8/24/2023 1:15	13.36
8/24/2023 1:30	13.44
8/24/2023 1:45	13.58
8/24/2023 2:00	13.72
8/24/2023 2:15	13.9
8/24/2023 2:30	14.02
8/24/2023 2:45	14.06
8/24/2023 3:00	14.06
8/24/2023 3:15	13.94
8/24/2023 3:30	13.54

Example data file two:

Timestamp	DOcon[mg/L]
8/22/2023 1:02	8.69
8/22/2023 2:02	8.7
8/22/2023 3:02	8.61
8/22/2023 4:02	8.45
8/22/2023 5:02	8.37
8/22/2023 6:05	8.17
8/22/2023 7:02	7.88
8/22/2023 8:02	7.84
8/22/2023 9:02	7.74
8/22/2023 10:02	7.67
8/22/2023 11:32	7.47
8/22/2023 12:24	9.04
8/22/2023 13:02	7.45
8/22/2023 14:02	7.32
8/22/2023 15:02	7.3
8/22/2023 16:02	7.51
8/22/2023 17:02	7.66
8/22/2023 18:05	7.83
8/22/2023 19:02	8.02
8/22/2023 20:02	8.45
8/22/2023 21:02	8.21
8/22/2023 22:02	8.47
8/22/2023 23:02	8.49
8/23/2023 1:02	8.3
8/23/2023 2:02	8.23
8/23/2023 3:02	8.3
8/23/2023 4:02	8.2
8/23/2023 5:02	8.3
8/23/2023 6:05	8.27
8/23/2023 7:02	8.15
8/23/2023 8:02	7.94
8/23/2023 9:02	7.86
8/23/2023 10:02	7.81
8/23/2023 19:02	8.6
8/23/2023 20:02	8.78
8/23/2023 21:02	8.85
8/23/2023 22:02	8.92
8/23/2023 23:02	8.9
8/24/2023 0:02	8.88
8/24/2023 1:02	8.83
8/24/2023 2:02	8.8
8/24/2023 3:02	8.64
8/24/2023 4:02	8.52
8/24/2023 5:02	8.47

[lbendlin · Accepted solution]

This was quite a bit harder than I like to admit.  I left my original approach (separate date and time dimension tables) in there but it doesn't work when you use field hierarchies in the x axis - the axis becomes categorical rather than continuous.  Go figure.

[DanCodiga]

Thanks @lbendlin ! Very helpful, to get me started here.

As a test, I deleted the Date and Time tables that you added to the data model, so that it only had the one new Timestamps table. It seems that the visual was not affected by this.

So as I understand it this solution consists of (a) creating a new 1-column table that merges the Timestamp columns of the two input tables; (b) adding 1:* relationships from the new table to the corresponding columns of the two input tables; and (c) then using the new table as the field for the x-axis in the plot. Correct? I learned a lot.

It works. But it is indeed more complicated than I was expecting. If anybody can suggest alternative solutions, please do!

p.s. This is probably a separate topic, but eventually I may want to do this same kind of overlay, with more than 2 time series (i.e. starting from say 3, or maybe 4, of the 2-column input files like the two that I shared here). So I have been looking at xVix Multi-Axes Chart https://xviz.com/visuals/multi-axes-chart/ as a possible solution. I wonder if this tool would require the the same kinds of data manipulations as for the two y-axes case described above, or perhaps the underlying data handling would be different.

[lbendlin]

You might be interested in this article

https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/

[DanCodiga]

Thank you @lbendlin . I will review that carefully. I also found this and it seems useful: https://radacad.com/power-bi-date-dimension-default-or-custom-is-it-confusing

[lbendlin]

There are many reasons for not using auto date/time  (mainly based on storage impact for your data model), and only very few scenarios where it is appropriate (after you scrubbed your data to make sure you don't have any bogus dates like "12/31/9999" )

[lbendlin]

A1: Not required, but highly recommended. What you really want is to disable all Date/Time Auto shenanigans, and to add a proper calendar table to your data model (which would eventually feed your visual's X axis)

A2:Line visuals show numerical values, and thus require an aggregation. This is by design

A3: See A1.