Plotting two sets of sensor data on same chart with time being the X-axis

[Sachintha]

I get some meter data from one of our factory tools in CSV format. The same tool has multiple 'meters', so I get different CSV files for each meter. The CSV has the format { TimeStamp, MeterData }. Time Stamp has ms granularity. However, Meter1 and Meter2 doesn't have the same time stamps - i.e., the first time stamp in Meter1 and Meter2 can be differnt, although, typically, within millisecond range difference. Log durations are typically 2-3 hours, and as such within the same day.

I need to plot these two meter values in the same line-chart.

But given that I can't create a DateTable (pretty much all the 'date' values of the time stamp will be identical), how do I link the two tables to use a common X-axis and have time in the X-axis?

Below are two sample data sets from Meter1 and Meter2:

METER 1

TimeStampMS,Meter1
2021/05/11 11:35:00.000,0.85415
2021/05/11 11:35:00.057,0.85945
2021/05/11 11:35:00.066,0.83735
2021/05/11 11:35:00.068,0.85945
2021/05/11 11:35:00.111,0.86025
2021/05/11 11:35:00.112,0.866
2021/05/11 11:35:00.220,0.84955
2021/05/11 11:35:00.828,0.8381
2021/05/11 11:35:00.830,0.85835
2021/05/11 11:36:00.000,0.86555
2021/05/11 11:36:00.003,0.877
2021/05/11 11:36:00.005,0.8572
2021/05/11 11:36:00.008,0.86555
2021/05/11 11:36:00.106,0.86555
2021/05/11 11:36:00.668,0.85835
2021/05/11 11:36:00.879,0.86635
2021/05/11 11:36:00.997,0.8549
2021/05/11 11:36:00.999,0.86025
2021/05/11 11:37:00.018,0.84345
2021/05/11 11:37:00.031,0.8713
2021/05/11 11:37:00.312,0.8713
2021/05/11 11:37:00.472,0.86025
2021/05/11 11:37:00.540,0.86555
2021/05/11 11:37:00.600,0.8442
2021/05/11 11:37:00.711,0.86065

METER 2

TimeStampMS,Meter2
2021/05/11 11:35:00.000,1.03765
2021/05/11 11:35:00.160,1.06475
2021/05/11 11:35:00.543,1.0468
2021/05/11 11:35:00.907,1.0693
2021/05/11 11:36:00.247,1.0605
2021/05/11 11:36:00.354,1.0468
2021/05/11 11:36:00.520,1.05135
2021/05/11 11:36:00.686,1.0468
2021/05/11 11:36:00.881,1.06015
2021/05/11 11:37:00.059,1.05135
2021/05/11 11:37:00.186,1.03765
2021/05/11 11:37:00.231,1.05595
2021/05/11 11:37:00.323,1.03765
2021/05/11 11:37:00.450,1.04225
2021/05/11 11:37:00.526,1.0331
2021/05/11 11:37:00.639,1.05595
2021/05/11 11:37:00.742,1.051
2021/05/11 11:37:00.885,1.0468
2021/05/11 11:38:00.038,1.051
2021/05/11 11:38:00.132,1.0468
2021/05/11 11:38:00.298,1.04225
2021/05/11 11:38:00.456,1.0514
2021/05/11 11:38:00.555,1.05555
2021/05/11 11:38:00.666,1.0422
2021/05/11 11:38:00.781,1.0514

[Sahir_Maharaj · Accepted solution]

Hello @Sachintha,

Can you please try this:

1. Since the timestamps in both meters do not exactly match, you'll need to create a common time table that rounds the timestamps to a consistent level. 

TimeTable = 
ADDCOLUMNS (
    GENERATESERIES (
        MIN ( 'Meter1'[TimeStampMS] ),
        MAX ( 'Meter1'[TimeStampMS] ),
        TIME (0, 0, 1)  -- Adjust the increment as needed
    ),
    "Time", [Value]
)
​

2. Align Meter Data with the Time Table

Meter1Value = 
CALCULATE (
    LASTNONBLANK ( 'Meter1'[Meter1], 1 ),
    'Meter1'[TimeStampMS] <= EARLIER ( 'TimeTable'[Time] ),
    TOPN (
        1,
        FILTER ( 'Meter1', 'Meter1'[TimeStampMS] <= 'TimeTable'[Time] ),
        ABS ( 'Meter1'[TimeStampMS] - 'TimeTable'[Time] ),
        ASC
    )
)

Meter2Value = 
CALCULATE (
    LASTNONBLANK ( 'Meter2'[Meter2], 1 ),
    'Meter2'[TimeStampMS] <= EARLIER ( 'TimeTable'[Time] ),
    TOPN (
        1,
        FILTER ( 'Meter2', 'Meter2'[TimeStampMS] <= 'TimeTable'[Time] ),
        ABS ( 'Meter2'[TimeStampMS] - 'TimeTable'[Time] ),
        ASC
    )
)

Now that you have a TimeTable with aligned data from both meters, you can create a line chart in Power BI.

Hope this helps. Should you require any further assistance, please do not hesitate to reach out to me.

[Sachintha]

Thank you.

This works, and in my case actually the 2nd part is not needed. All I need is a time table that links to the original two tables.