Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter Table by Max date time value

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

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

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

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    I don't understand what you want for output. What would your expected output from that sample data be?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        amitchandak
        Super User
        6 years ago

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

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi Anonymous 

     

    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!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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