Forum Discussion
Solved
Last reading
Hello, I have this dataset with different temperature readings for same items. Is it any way to only keep the last reading for each item? This would be the yellow records. Thanks
Go to Modeling and hit New Table.
NewTable = SUMMARIZE( FILTER( ADDCOLUMNS( TableName, "Last Reading", VAR lastreadingdate = CALCULATE( MAX(TableName[Date]), ALLEXCEPT(TableName, TableName[Item]) ) RETURN IF(TableName[Date] = lastreadingdate, TRUE, FALSE) ), [Last Reading] = TRUE ), TableName[Machine], TableName[Item], TableName[Date], TableName[Temperature] )
Hi JuliaYebra
Here is another light weight version as a calc column
MaxTemp =
VAR MaxDate =
CALCULATE (
MAX ( Table2[Date] ),
ALLEXCEPT ( Table2, Table2[Item] )
)
RETURN
CALCULATE (
VALUES ( Table2[Temperature] ),
Table2[Date] = MaxDate
)
It is actually a calc column. Below is the measure. Same idea expect that ALLEXCEPT() will not work here, so I am using ALL() + VALUES()
MaxTempMeasure = CALCULATE(
VALUES(Table2[Temperature]),
FILTER(
Table2,
Table2[Date] = CALCULATE(
MAX(Table2[Date]),
ALL(Table2),
VALUES(Table2[Item])
)
)
)
Here is the solution with calculated column again MaxTempCalcColumn = VAR MaxDate = CALCULATE ( MAX ( Table2[Date] ), ALLEXCEPT ( Table2, Table2[Item] ) ) RETURN CALCULATE ( VALUES ( Table2[Temperature] ), Table2[Date] = MaxDate )Essentially that same column is in my solution. I just nested it inside a calculated table, then used it to filter the table so only the max date rows are kept, then removed the calculated column because you don't actually need to see it after that.
That was ingenious Anonymous
