Filter value from a column

[Mark88]

Hi,

I have a data set of more then 1M data lines. Below is a sample of this data set. The ID column is a text field and the ID's appear multiple times because they are active on multiple measurement dates. The measurement date is not unique, because multiple ID's can have the same measurement date. The last column contains the temperature measured on the specific date/time.

ID	MeasurementDate	Temperature
63473G8615080341001231	15-1-2019 10:50	20,6
63473G8615080341001231	15-1-2019 11:00	20,6
63473G8615080341001231	15-1-2019 11:10	20,6
63473G8615080341001231	15-1-2019 11:20	20,6
63473G8615080341001231	15-1-2019 11:30	20,6
63473G8615080341001231	15-1-2019 11:40	20,6
63473G8615080341001231	15-1-2019 11:50	20,6
63473G8615080341001231	15-1-2019 12:00	20,6
63473G8615080341001231	15-1-2019 12:10	20,6
63473G8615080341001231	15-1-2019 12:20	20,5
63473G8615080341001232	15-1-2019 10:50	20,8
63473G8615080341001232	15-1-2019 11:00	20,8
63473G8615080341001232	15-1-2019 11:10	20,8
63473G8615080341001232	15-1-2019 11:20	20,8
63473G8615080341001232	15-1-2019 11:30	20,8
63473G8615080341001232	15-1-2019 11:40	20,8
63473G8615080341001232	15-1-2019 11:50	20,8
63473G8615080341001232	15-1-2019 12:00	20,8
63473G8615080341001232	15-1-2019 12:10	20,8
63473G8615080341001232	15-1-2019 12:20	20,9
63473G8615080341001233	15-1-2019 10:50	20,9
63473G8615080341001233	15-1-2019 11:00	20,9
63473G8615080341001233	15-1-2019 11:10	20,9
63473G8615080341001233	15-1-2019 11:20	20,9
63473G8615080341001233	15-1-2019 11:30	20,9
63473G8615080341001233	15-1-2019 11:40	20,9
63473G8615080341001233	15-1-2019 11:50	20,9
63473G8615080341001233	15-1-2019 12:00	20,9
63473G8615080341001233	15-1-2019 12:10	20,9
63473G8615080341001233	15-1-2019 12:20	20,8

I want to create a new column that only shows the last temperature measured by the ID. These are the bold temperatures in my example. Can you please advise what formula I can use to arrange this?

[v-yiruan-msft]

Hi @Mark88 ,

You can create a measure as below to get the last temperature:

Latest temperature = 
CALCULATE (
    MAX ( 'Measurement'[Temperature] ),
    FILTER (
        'Measurement',
        'Measurement'[ID] = MAX ( 'Measurement'[ID] )
            && 'Measurement'[MeasurementDate] = MAX ( 'Measurement'[MeasurementDate] )
    )
)

Best Regards

Rena

[AllisonKennedy]

Not sure what the end goal is, this could be done using MEASURES, but if you absolutely need it as a column please explain why so we can provide more helpful response. You may be able to achieve it using the EARLIER function inside a calculated COLUMN.

[Mark88]

@AllisonKennedyThe actual table I have contains more columns. I require this measure in a seperate column (in the same table) because it's part of a couple of measurements I need to implement in this table to prepare my data. Most of the other measurements I already found out myself, but this one I'm unable to solve.

So in order to get my final result, I require this measurement in in a new column in the same table. I already tried to work with the EARLIER function, but I can't make it work.

[AllisonKennedy · Accepted solution]

Try these calculated columns;
DateRank = RANKX(FILTER(TempTable,TempTable[ID]=EARLIER(TempTable[ID])),TempTable[MeasurementDate],,DESC)

LastTemperatureReading = MINX(FILTER(TempTable,TempTable[ID]=EARLIER(TempTable[ID])&&TempTable[DateRank]=1),TempTable[Temperature])

[Mark88]

@AllisonKennedyThanks a lot for your help! The RANKX formula helped me to solve the issue. The MINX formula also worked, but this showed me the last temperature in every cell of the column. I only needed the last temperature and the other cells being blank. I solved this via an IF formula.

Also thanks to @v-yiruan-msft and @amitchandak for your time!

[amitchandak]

@Mark88 , This should work as a measure along with ID

lastnonblankvalue(Table[MeasurementDate],Table[Temperature])

new Table

Summarize(Table,Table[ID], "Last Value",lastnonblankvalue(Table[MeasurementDate],Table[Temperature]))

New Measure =

Sumx(Summarize(Table,Table[ID], "Last Value",lastnonblankvalue(Table[MeasurementDate],Table[Temperature])),[Last Value])