Summarize a table Group by one column filtering another column a status and another by the max value

Question

Hi&nbsp;I have a table as follows&nbsp;Ident&nbsp; Review Status&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date123&nbsp; &nbsp; &nbsp;Complete&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/12/2019123&nbsp; &nbsp; &nbsp;Complete&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28/02/2020123&nbsp; &nbsp; &nbsp;Scheduled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;08/02/2020111&nbsp; &nbsp; &nbsp;Complete&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15/04/2020134&nbsp; &nbsp; &nbsp;Scheduled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30/04/2020&nbsp;&nbsp;&nbsp;I'd like to create a new table with only the latest complete reviews per Ident im assuming i need a combination of summarize, Group and filter but cant get it right. Can anyone help?&nbsp;&nbsp;

jstorm · Answer

Use the LASTDATE() function in a CALCULATE measure.

Ex.

CALCULATE(

SUM( 'Table'[Values] ),
LASTDATE( 'Table'[Date] )
)

This should only return the only latest value for each row in your matrix or table visual.

matt_hd · Answer

I’m not sure that’s what I’m looking for, I need a table really with distinct ident and date of latest completed

jstorm · Answer

Try this:&nbsp;SUMMARIZE(&nbsp;&nbsp;&nbsp; 'Table',&nbsp;&nbsp;&nbsp; [Ident],&nbsp;&nbsp;&nbsp; "Date", IF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Table'[ReviewStatus] = "Completed",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAX( 'Table'[Date] )&nbsp;&nbsp;&nbsp; ))

camargos88 · Answer

Hi&nbsp;Matt_HD&nbsp;,&nbsp;Try this code for a new table:&nbsp;T = ADDCOLUMNS(SUMMARIZE('Table'; 'Table'[Ident]; "Date"; MAX('Table'[Date])); "Review Status"; CALCULATE(DISTINCT('Table'[Review Status]); FILTER('Table'; 'Table'[Ident] = EARLIER('Table'[Ident]) &amp;&amp; [Date] = EARLIER([Date]))))&nbsp;Ricardo

matt_hd · Answer

Hi&nbsp;just to advise none of these solutions worked&nbsp;&nbsp;my resolution was to bring in the same table again with some filters on the query to give me a redacted data set, I then created a helper column and could create the new table I needed with this.Not particularly elegant but got the job done for now.

Forum Discussion

Summarize a table Group by one column filtering another column a status and another by the max value

5 Replies

Related content

Parameter in summarize

Don't Summarize....still summarizes...?

Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

Filter depending on another filter

Slicer Filtering Another slicer