Forum Discussion

JuliaYebra's avatar
JuliaYebra
Helper III
8 years ago
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
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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]
)
parry2k's avatar
parry2k
Super User
8 years ago

Hi nickchobotar

 

I guess it is a calculated measure not column?

nickchobotar's avatar
nickchobotar
Skilled Sharer
8 years ago

parry2k

 

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 
	)