Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter value from a column

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.

 

IDMeasurementDate                   Temperature                             
63473G861508034100123115-1-2019 10:5020,6
63473G861508034100123115-1-2019 11:0020,6
63473G861508034100123115-1-2019 11:1020,6
63473G861508034100123115-1-2019 11:2020,6
63473G861508034100123115-1-2019 11:3020,6
63473G861508034100123115-1-2019 11:4020,6
63473G861508034100123115-1-2019 11:5020,6
63473G861508034100123115-1-2019 12:0020,6
63473G861508034100123115-1-2019 12:1020,6
63473G861508034100123115-1-2019 12:2020,5
63473G861508034100123215-1-2019 10:5020,8
63473G861508034100123215-1-2019 11:0020,8
63473G861508034100123215-1-2019 11:1020,8
63473G861508034100123215-1-2019 11:2020,8
63473G861508034100123215-1-2019 11:3020,8
63473G861508034100123215-1-2019 11:4020,8
63473G861508034100123215-1-2019 11:5020,8
63473G861508034100123215-1-2019 12:0020,8
63473G861508034100123215-1-2019 12:1020,8
63473G861508034100123215-1-2019 12:2020,9
63473G861508034100123315-1-2019 10:5020,9
63473G861508034100123315-1-2019 11:0020,9
63473G861508034100123315-1-2019 11:1020,9
63473G861508034100123315-1-2019 11:2020,9
63473G861508034100123315-1-2019 11:3020,9
63473G861508034100123315-1-2019 11:4020,9
63473G861508034100123315-1-2019 11:5020,9
63473G861508034100123315-1-2019 12:0020,9
63473G861508034100123315-1-2019 12:1020,9
63473G861508034100123315-1-2019 12:2020,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?

Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago
    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])

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , 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])

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago
    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        AllisonKennedy
        Community Champion
        6 years ago
        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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    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