Forum Discussion

JrKoh's avatar
JrKoh
Frequent Visitor
4 years ago
Solved

Downsampling data

Hi all,

 

I want to downsample my data from every second to every minute/hour. I have splitted the Date and Time from DateTime and tried grouping it by per min/hour but I cannot figure out how to select, say 12:00:00am data in the 12:00:00pm - 12:00:59pm, and every minute data after. I just want to take out a data at a fixed interval without the need to sum, average or do any other operations to be shown in the visual.

 

DateTimeDateTimeminhourSensorValue
1/8/2021 12:00:00 pm1/8/202112:00:00 pm012A843
1/8/2021 12:00:01 pm1/8/202112:00:01 pm012A994
1/8/2021 12:00:02 pm1/8/202112:00:02 pm012A994
.....................
1/8/2021 12:00:03 pm1/8/202112:00:59 pm012A873
1/8/2021 12:01:00 pm 1/8/202112:01:00 pm112A692
.........  ......
1/8/2021 01:00:00 pm 1/8/202101:00:00 pm013A899

 

Any help or advice will be appreciated, thank you

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi JrKoh ,

     

    You can create a calculated column like

     

     

    Time = TIME(HOUR([DateTime]),MINUTE([DateTime]),0)

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • JrKoh's avatar
    JrKoh
    4 years ago

    Hi Stephen,

    With your solution in mind, I found a way to downsample the data. I created 2 calculated column, Time_1min and Time_H and wrote another DAX measure and put it into the chart filter. I then key in the number into the filter to determine which sampling:

     

    Downsample =
    SWITCH(
    TRUE(),
    SUM(Sensor[Time]) == SUM(Sensor[Time_H]),1,
    SUM(Sensor[Time]) == SUM(Sensor[Time_1min]),3
    )

     

    per hour
     

    per min

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi JrKoh ,

     

    You can create a calculated column like

     

     

    Time = TIME(HOUR([DateTime]),MINUTE([DateTime]),0)

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JrKoh's avatar
      JrKoh
      Frequent Visitor
      4 years ago

      Hi Stephen,

      With your solution in mind, I found a way to downsample the data. I created 2 calculated column, Time_1min and Time_H and wrote another DAX measure and put it into the chart filter. I then key in the number into the filter to determine which sampling:

       

      Downsample =
      SWITCH(
      TRUE(),
      SUM(Sensor[Time]) == SUM(Sensor[Time_H]),1,
      SUM(Sensor[Time]) == SUM(Sensor[Time_1min]),3
      )

       

      per hour
       

      per min