Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
JuliaYebra
Helper III
Helper III

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

1.png

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
v-huizhn-msft
Employee
Employee

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

nickchobotar
Skilled Sharer
Skilled Sharer

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 
	)

image.png

Hi @nickchobotar

 

I guess it is a calculated measure not column?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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



image.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




That was ingenious @KHorseman


Regards
Zubair

Please try my custom visuals
fhill
Resident Rockstar
Resident Rockstar

 

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

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




erik_tarnvik
Solution Specialist
Solution Specialist

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:

 

image.png

 

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:

 

image.png

 

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.

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", "")


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

While I was writing my Power Query based response, @KHorseman 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.

KHorseman
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.