Forum Discussion
Appending different columns
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
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.