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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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]
)
  • erik_tarnvik's avatar
    erik_tarnvik
    Solution Specialist
    8 years ago

    Indeed there is. Go to Power Query to edit your data import. Select both your Machine and your Item column. Click Transform - Group By. In the dialog, change New Column Date to Date, Operation to Max and Column to Date. That should give you this:

     

     

    But, we lost the temperature. We need to bring it back in. Load your data again using Recent Sources. I called your table Reading, so this gives me a Reading (2) query in the Query pane. Select your original query (Reading in my example) in the query pane and click Merge Queries. In the top portion, select the Machine, Item and Date columns (ctrl-click on each column). Then select your Reading 2 query in the drop down further down and select all three same columns. Press OK. You should now have this:

     

     

    Click the small icon in the upper right corner of the Reading (2) column header to expand the tables. Select only the Temp item in the resulting pop-up. Rename the resulting Column to Temp. 

     

    The only remaining thing now is to right click the Reading (2) query and unselect the Enable Load menu item. This prevents this temporary query to from being loaded to your data model. Click close and apply and your should have the result you want.

    • parry2k's avatar
      parry2k
      Super User
      8 years ago

      here simple formula to add new column to identify last reading (most recent)

       

      Last Reading = 
var r = 
      RANKX(
      FILTER(Table1, Table1[Machine] = EARLIER(Table1[Machine]) && 
      Table1[Item] = EARLIER(Table1[Item])), Table1[Date], , DESC, DENSE
      )
return if(r = 1, "Yes", "")
    • erik_tarnvik's avatar
      erik_tarnvik
      Solution Specialist
      8 years ago

      While I was writing my Power Query based response, Anonymous provided a DAX based method that also works.

       

      One possible benefit of the Power Query method is that it doesn't clutter your data model with unecessary data. On the other hand, perhaps you need that data for some purpose and in that case, I would go with the DAX method.

  • fhill's avatar
    fhill
    Resident Rockstar
    8 years ago

     

    There are probably several ways to do this, but here's one option:  

     

    DAX Column 1:  This calculates the MAX DateTime for each Item && Machine grouping.

    LastReading_DateTime = CALCULATE(MAX(Table2[DateTime]), FILTER(ALL(Table2), Table2[Machine] = EARLIER(Table2[Machine]) && Table2[Item] = EARLIER(Table2[Item])))

     

    DAX Column 2:  This only returns a TEMP if the LastReading_DateTime matches the original table's DateTime

    LastReading_Temp = IF (Table2[DateTime] = Table2[LastReading_DateTime],Table2[Temp],BLANK())

     

    (Sorry my DateTime is descending, but you get the idea.)

  • nickchobotar's avatar
    nickchobotar
    Skilled Sharer
    8 years ago

    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 
	)

      • 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 
	)



         

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi JuliaYebra,

    Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will find solution easily here.

    Best Regards,

    Angelia