Forum Discussion
Summarizing filtered row values in DAX and sorting by Datetime
Hello, I am pretty new to DAX, so please excuse me if what I am trying to show you is not 100% clear. I have a table in my dataset that contains the columns shown below:
| DateTimeStartOfHour | SensorName | Value |
| 01.10.2020 00:00:00 | sensor_100 | 100 |
| 01.10.2020 00:00:00 | sensor_200 | 200 |
| 01.10.2020 00:00:00 | sensor_201 | 30 |
| 01.10.2020 00:00:00 | sensor_300 | 10 |
| 01.10.2020 00:00:00 | sensor_400 | 900 |
| 01.10.2020 01:00:00 | sensor_100 | 105 |
| 01.10.2020 01:00:00 | sensor_200 | 198 |
| 01.10.2020 01:00:00 | sensor_201 | 32 |
| 01.10.2020 01:00:00 | sensor_300 | 11 |
| 01.10.2020 01:00:00 | sensor_400 | 924 |
| 01.10.2020 02:00:00 | sensor_100 | 103 |
| 01.10.2020 02:00:00 | sensor_200 | 202 |
| 01.10.2020 02:00:00 | sensor_201 | 29 |
What I want to do is to create a measure (if that is the right way to go) that filters out the values of two of the sensors (200 and 201) and summarizes them as it makes sense to display them together in the visualizations. I need the context of the datetime column to correspond with the sum of the values. The result would look something like this:
| DateTimeStartOfHour | SensorName | Value |
| 01.10.2020 00:00:00 | sum of sensor_200 and sensor_201 | 230 |
| 01.10.2020 01:00:00 | sum of sensor_200 and sensor_201 | 233 |
| 01.10.2020 02:00:00 | sum of sensor_200 and sensor_201 | 231 |
I was trying to do it with a CALCULATE formula using SUM and FILTER, but it didnt work out. Would really appreciate if somebody could help me out 🙂
1 Reply
Hi Anonymous ,
try this.
sum of sensor_200 and sensor_201 = CALCULATE(SUM('Table'[Value]), 'Table'[SensorName] in {"sensor_200","sensor_201"} )
