Forum Discussion
Helper IIIFilter on Latest Date
I have a table where I would like to check the average value on latest date but these average values are data points for different description,
so for example I have this table:
Table A
| Id | Machines |
| 1 | MachineA |
| 2 | MachineA |
| 3 | MachineA |
| 4 | MachineA |
Table B
| ID | timeseriesId | Description |
| 1 | 34333 | Pressure rate |
| 1 | 34339 | Flow rate |
| 1 | 44343 | Temperature |
| 2 | 22211 | Pressure rate |
| 2 | 33222 | Flow rate |
Table C
| TimeseriesId | Value | Timestamp |
| 34333 | 34 | 2021-12-12 12:09:12 |
| 34333 | 43 | 2021-12-21 10:00:00 |
| 34333 | 12 | 2021-01-10 21:09:12 |
| 44343 | 19 | 2021-12-12 12:09:12 |
| 22211 | 21 | 2021-12-10 12:09:12 |
| 22211 | 91 | 2021-11-11 12:09:12 |
So if you see in these three tables(all conneceted by one-many relationship) I am interested in getting the Pressure rate recorded latest for each machine. So in here I am interested to create a measure which gives me value (43) as that is the latest Pressure rate recorded for machineA and 21 which is latest pressure rate recorded for machineB. Similary I would like this for other machines. How can I do this??
Hi PowerrrBrrr ,
You can try this measure:
Pressure Rate = CALCULATE(SUM('Table C'[Value]),'Table B'[Description]="Pressure rate",FILTER('Table C','Table C'[Timestamp]=MAX('Table C'[Timestamp])))If I answered your question, please mark it as a solution to help other members find it more quickly.jppv20 I dont get the result. I took a table visual and added ID timestamp and pressure rate, and I get blank . there is no values in any column
4 Replies
PowerrrBrrr Try this:
Measure=var LastTime=LASTNONBLANK('Table C'[Timestamp],SUM('Table C'[Value]))
return CALCULATE(SUM('Table C'[Value]),FILTER('Table C','Table C'[Timestamp]=LastTime&&'Table B'[Description]="Pressure rate"))
PowerrrBrrrHere tableB[Desciption ] gives an error in measure. it seems to be not available when creating measure
jppv20Solution Sage
Hi PowerrrBrrr ,
You can try this measure:
Pressure Rate = CALCULATE(SUM('Table C'[Value]),'Table B'[Description]="Pressure rate",FILTER('Table C','Table C'[Timestamp]=MAX('Table C'[Timestamp])))If I answered your question, please mark it as a solution to help other members find it more quickly.- PowerrrBrrr
jppv20 I dont get the result. I took a table visual and added ID timestamp and pressure rate, and I get blank . there is no values in any column
