How to find rows associated with latest date value

[itspossible]

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!

[v-juanli-msft · Accepted solution]

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

[richardverburg]

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

[itspossible]

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?

[itspossible]

So close to a solution. Anyone have any suggestions?

[itspossible]

bump

[Zubair_Muhammad]

@itspossible

Your dropbox link doesn't work

Nevertheless, please see if this blog post is useful

http://www.excelnaccess.com/grouping-summarizing-dax-vs-power-query-m/

[Greg_Deckler]

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

[itspossible]

Thank you. File is attached in dropbox link.