Selecting Rows by Max Date and something else

[stribor45]

I have table where multiple rows with same ID may exist but with  different or sometimes same values in Date column.  Of course I can group this by ID and select ones with MAX date however since sometimes Date column can end up having same dates so I am wondering how to choose which row to keep. I have other columns in this table I can utilize to make that decision only not quite sure how to do it.

Any suggestions?

[Nasif_Azam]

Hey @stribor45 ,

To select rows by MAX(Date) and a secondary condition when there are ties, you can use techniques like row_number, rank, or filtering with a join/subquery, depending on your SQL dialect.

Method 1: ROW_NUMBER() with ORDER BY Multiple Columns

If you have a column to break ties (e.g., Status, Amount, Priority), use ROW_NUMBER() to pick just one:

WITH RankedData AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY ID
           ORDER BY Date DESC, Status DESC, Amount DESC
         ) AS rn
  FROM your_table
)
SELECT *
FROM RankedData
WHERE rn = 1;

You can replace Status DESC, Amount DESC with whatever additional logic makes a row preferable.

Method 2: RANK() for All Ties at Max Date, Then Filter

If you want to keep all rows that tie for the best Date and your secondary preference, use RANK():

WITH RankedData AS (
  SELECT *,
         RANK() OVER (
           PARTITION BY ID
           ORDER BY Date DESC, Status DESC
         ) AS rnk
  FROM your_table
)
SELECT *
FROM RankedData
WHERE rnk = 1;

This returns all top-ranked rows per ID.

Method 3: Subquery with MAX(Date), Then Join on Other Logic

If you don’t want window functions:

SELECT t.*
FROM your_table t
JOIN (
    SELECT ID, MAX(Date) AS MaxDate
    FROM your_table
    GROUP BY ID
) maxed ON t.ID = maxed.ID AND t.Date = maxed.MaxDate
WHERE t.Status = 'PreferredStatus'; -- or any other condition

This returns only rows with the max date and your custom logic.

Deciding “Which Row to Keep”

Use these heuristics based on your context:

• Pick the most recent DateTime + Highest Priority.

• Prefer a specific Status like 'Active' or 'Completed'.

• Use ISNULL(Column, 0) or custom scoring if the preference is complex.

You can also chain ORDER BY columns in the ROW_NUMBER() clause to incorporate multiple layers of preference.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[speedramps]

Here is a Power Query  solution as question.

Please please please click thumbs up.  I deserve it !

Also clcik [accept solution] it is works (which it does)

Create you test data

Create a new table which referfnces your original table

Group it by ID with Max date

Merge as new query (to crate a new table)

using ID and Date

with a Right Outer join (all from second, matching the first)

Reference that table to create a new table

Group it by ID with min decision

Merge as new query (to create a new table)

join using ID and Decision

with a Right outer hoin

It works in the reports

Download the PBIX from Onedrive here

Click here 

Note that my solution returns the correct value for ID 88888.

Please check that other member's solutions also handle that tricky scenario  

[speedramps]

Grhhhhh.  So they tels me after I spent 30 mins carefully creating a DAX example   🙄

[speedramps]

Try this

Answer = 
CALCULATE(
  MAX(yourdata[Date]),
  ALLEXCEPT(yourdata,yourdata[ID])
)

Please click [thumbs up] because I have tried to help.

Then click [accept solution] if it work. Thank you !

I want to help you more but your description is too vague.  

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[stribor45]

I shared small example below. Thanks for sharing that information however I was curious about how to solve this in power query rather then DAX

[AlienSx]

Table.Max(table as table, comparisonCriteria as any, optional default as any) as any

write your own comparisonCriteria

[ronrsnfld]

How are you choosing that row now?

In general, you would just add to the appropriate logic to the condition argument of the Table.SelectRows function.

[stribor45]

For example the top is the table I start with and below it is the table I want to end up with. If the date column is the same for the the particular id then I keep the roiw with lowest number in Decision column

[AlienSx · Accepted solution]

Table.FromRecords(
    Table.Group(Source, "id", {"recs", (x) => Table.Max(x, {"Date", (w) => - w[Decision column]})})[recs]
)

[ronrsnfld]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyBGIzfUNDfSMDIxDfUClWByHrhCZrBJY1BQG4XmNUrTBJZ7CkCUzSGCxpBgJwnUaoOs1BAMhzQ5U0U4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Type = _t, Date = _t, #"Decision column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Type", type text}, {"Date", type date}, {"Decision column", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
        {"MaxDate", (t)=> [a=Table.SelectRows(t, each [Date]=List.Max(t[Date])),
                           b=Table.SelectRows(a, each [Decision column]=List.Min(a[Decision column]))][b], 
                          type table [id=nullable number, Type=nullable text, Date=nullable date, Decision column=nullable number]}}),
    
    #"Expanded MaxDate" = Table.ExpandTableColumn(#"Grouped Rows", "MaxDate", {"Type", "Date", "Decision column"})
in
    #"Expanded MaxDate"

From your data: