Forum Discussion

Sachintha's avatar
Sachintha
Icon for Helper III rankHelper III
2 years ago
Solved

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

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
Need Help
Show and Tell
Tips and Tricks
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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.

     

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Sachintha
      Icon for Helper III rankHelper III
      2 years ago

      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.