Max Date Values

I tested both approaches on a 100'000 record fact table, running each query twice.

My query, using the self-join approach ran in 0.033 ms and in 0.27 ms respectively, and your query, using a "group and filter sub-table" approach, ran in 27.49 ms and 10.92 ms respectively. 

My big fact table of 100'000 records, consisted of columns "System ID", "Threshold Level", "Date", and "Value", where "System ID" and "Threshold Level" of each record was a random pick from the integer interval [1 .. 5], Date was a date between 2023-01-01 and 2023-01-01, and Value was a random number between 0 and 10. 

code to generate the random table is given below just in case you want to test also: 

/*
    This query will generate a big table with columns 
        "System ID" = a dimension key where possible values are defined in ListSystems
        "Threshold Level" = a dimension key where possible values are defined in ListThreshold, 
        "Date" = a date on or between pStartDate and pEndDate
        "Value" = a random number between 0 and 10

    The only input required is
        * pStartDate = the earliest date that should appear in the Date column
        * pEndDate = the latest date that should appear in the Date column
        * pNbrOfTransactions = the desired number of transaction records in the final table

    The way the big fact table is constructed: 
    Each dimension key pair (System ID, Threshold Level) will appear for all dates in the date range. 
    Hence, if we need X transactions in the final table, and we have Y dates, 
    then we need X/Y = Z dimension key pairs (System ID, Threshold Level)
    So the steps to construct the final table are:
        1. Calculate pNumberOfDays = the number of days between pStartDate and pEndDate
        2. Calculate the required number of Dimension Keys: pNumberOfDimensionKeys = pNumberOfTransactions / pNumberOfDays
        3. Create a table with "System ID" and "Threshold Level" as columns, having pNumberOfDimensionKeys records
        4. For each record in that table, add the list of all dates {pStartDate .. pEndDate}
        5. Expand the list as new rows
        6. Add a column "Value" and fill with a random number between 0 and 10
*/



let
    //define the starting parameters
    pStartDate = #date(2023,1,1),
    pEndDate = #date(2023,2,1),
    pNbrOfTransactions = 100000,  //the desired number of rows in the final table

    //define the list of valid keys for System ID and for Threshold Level
    ListSystem = {1 .. 5},
    ListThreshold = {1 .. 5},

    //all required input data are known, so start the generation process

    //first calculate some counters
    pNumberOfDays = Duration.Days(pEndDate-pStartDate) + 1,
    pSystemCount = List.Count(ListSystem),
    pThresholdCount = List.Count(ListThreshold),

    pNumberOfDimensionKeys = Number.RoundUp(pNbrOfTransactions / pNumberOfDays),

    //generate a list of System keys randomly picked from ListSystem
    ShuffledListSystems = List.Generate(
        () => 1, each _ <= pNumberOfDimensionKeys, each _ + 1, 
        each ListSystem{Number.RoundDown(Number.RandomBetween(0, pSystemCount))}
        ),
        
    //generate a list of Threshold keys randomly picked from ListThreshold
    ShuffledListThresholds = List.Generate(
        () => 1, each _ <= pNumberOfDimensionKeys, each _ + 1, 
        each ListThreshold{Number.RoundDown(Number.RandomBetween(0, pThresholdCount))}
        ),

    //create a table by adding ShuffledListThresholds and ShuffledListSystems together as columns
    Table1 = Table.FromColumns(
        {ShuffledListSystems, ShuffledListThresholds},
        type table[System ID = text, Threshold Level = text]
        ),

    //for each record in Table1, add all dates as a list    
    TableWithDates = Table.AddColumn(Table1, "Date", each List.Dates(pStartDate, pNumberOfDays, #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(TableWithDates, "Date"),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Value", each Number.RandomBetween(0,10)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Value", type number}})
in
    #"Changed Type1"