Help with Identifying the Most Recent Contract

I would do this in SQL, either upstream in Azure or as a custom query in Power Query. Something like this:

WITH ranked AS (
    SELECT
        AccountID,
        ContractID,
        StartDate,
        EndDate,
        Status,
        ROW_NUMBER() OVER (
            PARTITION BY AccountID
            ORDER BY
                CASE Status
                    WHEN 'Active' THEN 1
                    WHEN 'Future' THEN 2
                    ELSE               3
                END,
                /* Tie-breakers */
                CASE WHEN Status = 'Past'   THEN StartDate END DESC, -- last Past
                CASE WHEN Status = 'Future' THEN StartDate END ASC   -- next Future
        ) AS rn
    FROM YourSourceTableHere
)
SELECT
    AccountID, ContractID, Status, StartDate, EndDate
FROM ranked
WHERE rn = 1;

Hi DerhakRon ,
Here's another M-Code solution. I'll leave the M-Code and the image of the output below for your reference. Thanks

Here's the M-Code:
let
Source = #table(
{"AccountID", "ContractID", "Status", "StartDate", "EndDate"},
{
{1, 1, "Future", #date(2026, 1, 1), #date(2027, 12, 31)},
{1, 2, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{1, 3, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{2, 4, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{2, 5, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{3, 6, "Future", #date(2026, 1, 1), #date(2027, 12, 31)},
{3, 7, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{4, 8, "Future", #date(2026, 1, 1), #date(2027, 12, 31)},
{4, 9, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{5,10, "Future", #date(2027, 1, 1), #date(2027, 12, 31)},
{5,11, "Future", #date(2026, 1, 1), #date(2026, 12, 31)},
{6,12, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{7,13, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{7,14, "Past", #date(2023, 1, 1), #date(2023, 12, 31)}
}
),
ColNames = List.Skip ( Table.ColumnNames ( Source ) , 1 ),
#"Grouped Rows" = Table.Group(Source, {"AccountID"}, {{"AllTable", each _, type table [AccountID=number, ContractID=number, Status=text, StartDate=date, EndDate=date]}}),
#"Group Fx" = Table.TransformColumns ( #"Grouped Rows" , {"AllTable" , each if List.Contains ( _[Status] , "Active" ) then Table.SelectRows ( _ , each [Status] = "Active" ) else if List.ContainsAll ( {"Future"} , _[Status] ) then Table.LastN( _ , 1 ) else Table.FirstN ( _ , 1 ) } ),
#"Expanded AllTable" = Table.ExpandTableColumn(#"Group Fx", "AllTable", ColNames, ColNames)
in
#"Expanded AllTable"

Hi DerhakRon 

To efficiently filter your 24-million-row Contract table in Power Query (especially with a foldable query against Azure SQL), you can use a grouping and conditional filtering approach that mimics your DAX logic. The key is to group by AccountID and then apply conditional logic within each group to retain only the contract that fits your priority criteria: (1) if there's an Active contract, keep the most recent Active one; (2) if only Past contracts, keep the latest by StartDate; (3) if only Future contracts, keep the one with the soonest upcoming StartDate; (4) if both Future and Past exist but no Active, still pick the soonest Future one. In Power Query (M), this typically involves using Table.Group to nest contract records per account, followed by a custom column using Table.SelectRows and Table.Sort logic to apply your filtering rules within each subgroup. While Power Query itself may not support native SQL folding with this entire logic chain, you can try using Table.Group with as few transformations as possible inside the sub-tables, or even consider building the logic as a SQL view in Azure and using Power BI to connect to that filtered view instead. This offloads the filtering to the server and ensures scalability. If performance is still critical, you might consider creating a SQL stored procedure or computed view that encapsulates this logic using ROW_NUMBER() partitioned by AccountID with CASE statements prioritizing status. That would yield a much leaner dataset to import or query directly into Power BI.