Filter Table by Max date time value

[Amardeep100115]

Hi,

this is a survey data where people have submitted multiple times survey andwant filter survey by most recent not first survey

i tried to get it by grouping Completion date by MAX but no use

Please guide

Sample data

[v-diye-msft]

Hi @Amardeep100115 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[Amardeep100115 · Accepted solution]

@v-diye-msft 

Yeah,

got the answer for this 

please see the below m-code which i used for my query

#"Grouped Rows" = Table.Group(Source, {"Email"}, {{"Max Completion Time", each List.Max([Completion time]), type datetime}, {"All Rows", each _, type table [Source=text, ID=text, Start time=datetime, Completion time=datetime]}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Source", "ID", "Start time", "Completion time", "}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded All Rows", "Keep", each if [Max Completion Time] = [Completion time] then "Keep" else "Remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Keep] = "Keep")),

[Greg_Deckler]

I don't understand what you want for output. What would your expected output from that sample data be?

[Amardeep100115]

suppose if i have submitted survey a hour back and i changed my mind and resubmitted again same survey with another details. here i want to filter most recent survey by datetime. means my second survey should be there not earlier one

[amitchandak]

@Amardeep100115 , can not say without looking at data, but something like this

Measure =
VAR __id = MAX ( 'Table'[ID] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[ID] = __id )
RETURN CALCULATE ( Max ( 'Table'[Text] ), VALUES ( 'Table'[ID ), 'Table'[ID] = __id, 'Table'[date] = __date )

[Greg_Deckler]

Maybe https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434