Forum Discussion
How to find rows associated with latest date value
Hello! I've tried my hand at a number of solutions offered in the forums for similar topics but haven't succeeded - mostly because I'm extremely new to DAX/Powerquery/M.
Basically, I have a table with facebook channel data, where each post on my page has several rows - one row per day the post was active. All I need is a table or way to parse this given table that only selects all unique posts (along with all the other columns associated with them) for the latest date. The latest date is different for each post, of course. Can someone please outline the steps required and the code to use in order to achieve this? I have attached sample table below
Basically, for each unique instance of "message" I only want to retain the latest date in the column "db_date". And to ensure all other columns carry through. Thanks!
Hi itspossible
From the screenshot, it seems unique "post id" associated with each unique instance of "message", so i use "post id" as a test.
create a new table from your table
Table = FILTER(ALL(Table1),[date]=CALCULATE(MAX(Table1[date]),ALLEXCEPT(Table1,Table1[post id])))
Original table
New created table
Best Regards
Maggie
8 Replies
v-juanli-msftCommunity Support
Hi itspossible
From the screenshot, it seems unique "post id" associated with each unique instance of "message", so i use "post id" as a test.
create a new table from your table
Table = FILTER(ALL(Table1),[date]=CALCULATE(MAX(Table1[date]),ALLEXCEPT(Table1,Table1[post id])))
Original table
New created table
Best Regards
Maggie
- Greg_Deckler
Community Champion
Can you post example data that can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thank you. File is attached in dropbox link.
richardverburgHelper I
You can create a new table with the SUMMARIZE function. You get a table with unique posts with a MAX date. So you will have the maximum DB_DATE per post.
Create a new table under the tab modeling and enter the following code:
POST_WITH_MAX_DATE = SUMMARIZE(Sheet1; Sheet1[type]; Sheet1[post_id]; "Total Value"; max(Sheet1[db_date]))
Hi there, while the code provided works perfectly for those 3 columns referenced in it, it fails its intended purpose when I add the other columns into the query - particularly shares_count, comments_count, likes_count and reactions_total_count. I am back to seeing multiple entries for post_id. How can I fix this?
So close to a solution. Anyone have any suggestions?
