Forum Discussion

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

In "Body sensor1", "Body sensor2", "Body sensor3"

create custom columns named "Sensor" and rename column "body" as "value" respectively in three tables,

 

In "Ventilation sensor" table, create custom columns named "pir" and "Sensor", also rename column "airflow" as "value"

 

Then append four tables

 

 

Best Regards

Maggie

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, v-juanli-msft

Thank you for your reply!

 

Even if the sensors don't send values at the same time, Is there a way to Visualize all the sensor values at the same time? So that I can see how much Airflow the ventilation sensor sends when one or more than one person is at the room?
Maybe by taking the average time or by using time intervals for example, in a 10 minutes interval?
I don't know how to do it, but the idea is to visualize the Airflow and body values at the same time.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    I can work out with the requirement: "using time intervals for example, in a 10 minutes interval"

     

    you could slicer the time in a time interval as below

     

    To get result above, go to Queries Editor, add custom columns in the "Append1" table

    Add custome columns step by step

    Date =DateTime.Date([time])
Time.1 =DateTime.Time([time])
Hour= Time.Hour([Time.1])
Minute= Time.Minute([Time.1])
Length= Text.Length(Text.From([Minute], "en-US"))

Transform->Extract-> the first character->count(1),
then Transform->change column “the first character “ type to “number”

Custom =if [Length] > 1 then [First Characters] else 0
Custom.1= Text.Combine({Text.From([Hour], "en-US"),Text.Combine({Text.From([Custom], "en-US"),"0"},""),"00"}, ":")

     

    Create a table like this in excel and import into power bi

    time interval refernce
    9:30:00 9:30:00 AM ~ 9:40:00 AM
    9:40:00 9:40:00 AM ~ 9:50:00 AM
    9:50:00 9:50:00 AM ~ 10:00:00 AM
    10:00:00 10:00:00 AM ~ 10:10:00 AM
    10:10:00 10:10:00 AM ~ 10:20:00 AM
    10:20:00 10:20:00 AM ~ 10:30:00 AM
    10:30:00 10:30:00 AM ~ 10:40:00 AM
    10:40:00 10:40:00 AM ~ 10:50:00 AM
    10:50:00 10:50:00 AM ~ 11:00:00 AM
    11:00:00 11:00:00 AM ~ 11:10:00 AM
    11:10:00 11:10:00 AM ~ 11:20:00 AM
    11:20:00 11:20:00 AM ~ 11:30:00 AM

    Then go to  Queries Editor, change the data type of "time interval" to Text.

    Create a relationship betwwen this table and "Append1" table

     

    Finally, add "time interval" in the slicer

     

    Best Regards

    Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    But it is not clear for me for the other requirements.

    "see how much Airflow the ventilation sensor sends when one or more than one person is at the room",

    "the average time in a 10 minutes interval"

     

    If these above you have problem to do, I would suggest you to provide details,

    eg. raise an example based on your data.

    You could also open another thread to let more people see your problem and help you.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-juanli-msft

      Below, I tried to explain my question in a better way.

       

      I have 3 body sensor tables that contain date and body values, and I have another ventilation sensor table which has a date, airflow and Pir values
      They all have different values, I appended all the tables together.
      The appended query has a date, airflow, body and PIR values.

      The ventilation sensor sends airflow values every 10 mins and the body sensors send body values once or more than once in 10 mins.

      To visualize how much airflow the ventilation sends when there are one or more people in the room at the same time, I created a calculated column with 10 mins time interval
      time2 = DATE(YEAR(CombinedTable[Date]),MONTH(CombinedTable[Date]),DAY(CombinedTable[Date]))+TIME(HOUR(CombinedTable[Date]),FLOOR(MINUTE(CombinedTable[Date]),10),0)
      To add the body values in 10 mins time interval I created a measure
      Measure = CALCULATE(SUM(CombinedTable[Body]),IF(CombinedTable[Body] > 0,1,0),LASTNONBLANK(CombinedTable[time2],""))

      when the body sensor sends value 1 it means someone is in the room, sometimes it can send values more than once in 10 mins interval when it senses a body appearance. I want to get only 1 value in 10 mins for each body sensor so when I visualize the data I can see how many people are in the room, and it shouldn't be more than 3.

      For example, if one of the body sensors sends value 1 more than once in 10 mins interval I only want to take 1 of them
      Below is a sample of my visualization,
      It shows 5 body values because it adds them all even when it sends values more than once. but the correct value should only show 3 body values because we have 3 body sensors.