Forum Discussion

khartleymb's avatar
khartleymb
Regular Visitor
2 years ago
Solved

Two time series tables from same sensors same plot

Hi all, 

I'm working on a problem where I have two tables of hydrologic sensor data and need them on the same plot. 

One table is a one month of real time data measured every 5 minutes. The Fields are simply SensorID, Date time, flow and level. 

The other table is historical data of those same sensors, averaged per day, with highest, lowest and average flows. (SensorID, Date (day only), upper, medium and lower averages. 

I need to plot them on the same line graph, and it's driving me crazy. 

Thanks, 

Keith

Need Help
  • khartleymb's avatar
    khartleymb
    2 years ago

    I figured it out!

    I needed a third main "table" that would allow me to relate to both datasets (so "fact" table, then two tables of the sensors, historical and real time sensor info) 
    The other thing I needed to make a "DATE" table of only date to relate to both the historical and fact table to normalize date between the two tables. To get a date table I used a measure of DAX - with (

    datetbl = CALENDARAUTO()) 

    With everything related I could plot the historical (max and min for each day)  and real time datasets by station ID - also can link to a map and water basin! 

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Hi khartleymb 

     

    How do you envisage this plot to look like?  Why do the data sets need to be on the same plot?

     

    If you have 2 sets of data with very different time granularity, how are they to be plotted together?

     

    The real time data will have 100's of data points across a month but the historical data has how many days of data?

     

    What's the default x-axis scale?  Days or minutes?

     

    Why not just plot them on different charts?

     

    Phil

    • khartleymb's avatar
      khartleymb
      Regular Visitor
      2 years ago

      Hi Phil, 

      I'm trying to re-create a plot series from an excel /VBA pdf workflow to a published powerBI service. The end result is plots that allow a user to see different river flow levels both in current time vs historical data (via slicers) 

      The data needs to be on the same plot as it would show the past flow levels vs the current flow level. 

      It looks something like this - assiniboine_river_flows.pdf (gov.mb.ca)
      The default X axis is in days. 

      Thanks, 

      Keith 

  • khartleymb's avatar
    khartleymb
    Regular Visitor
    2 years ago

    I figured it out!

    I needed a third main "table" that would allow me to relate to both datasets (so "fact" table, then two tables of the sensors, historical and real time sensor info) 
    The other thing I needed to make a "DATE" table of only date to relate to both the historical and fact table to normalize date between the two tables. To get a date table I used a measure of DAX - with (

    datetbl = CALENDARAUTO()) 

    With everything related I could plot the historical (max and min for each day)  and real time datasets by station ID - also can link to a map and water basin!