How do I create a global max value that references another measure?

pacificnwp 

Hi, there would be two solutions.

One with DAX, first create a Calculated Column

QuarterKey = 'Sales'[Year] * 10 + 'Sales'[Quarter]

then create 3 DAX Measures

Quarters to Date = 
CALCULATE (
    DISTINCTCOUNT ( 'sales'[QuarterKey] ),
    ALLEXCEPT ( 'sales', 'sales'[Name] )
)

MaxQuarters = 
MAXX(
    ADDCOLUMNS(
        VALUES('Sales'[Name]),
        "RepQuarters", CALCULATE(
            DISTINCTCOUNT('Sales'[QuarterKey]),
            ALL('Sales')
        )
    ),
    [RepQuarters]
)

IsActive = 
IF ( [Quarters to Date] = [MaxQuarters], 1, 0 )

apply them on table and you will see 'Mike Smith' should be filtered out. 

Or in Power Query, you can use below logic to filter out the above two person as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZSxCoMwFEV/RTI7tMnzDzoJTg4dxCGV0AZjUqr/T1NQCr7W5A5ClHPJeeGarhO19qa4BCNK0T7NYLWz8xJfzvGRJynX5aStF32ZxUuQVyBPAK9AfwX6K9Bfgf4E+hPoT6A/gf4V6F/t/Rs7mqKd7PLYJ+SfhmYl8D0UnCAowZqalfgsXQjjTQ8jlsL2waZnLU8mWM+zEtgcW9ex82J/yNU6V9TBmzl+bbTXd/NamV91TOMSw0EZysdZBdM4619eBNgBGJfV7hhnnUvjgDvYG3axHuPbvQqcPbuK0/h33P4N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Role Title" = _t, Area = _t, Year = _t, Quarter = _t, #"Calc Schema" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    Custom1 = Table.SelectRows(#"Changed Type", each  [Calc Schema] = "main" and [Year] >= 2023),

    // Step 2: keep unique Rep/Quarter Year
    UniqueRepQuarter = Table.Distinct( Table.SelectColumns(Custom1, {"Name", "Year","Quarter"}) ),
    // Step 3: group by rep
    Grouped = Table.Group(
        UniqueRepQuarter,
        {"Name"},
        {{"QuarterCount", each Table.RowCount(_), Int64.Type}}
    ),
    // Step 4: find max quarters
    MaxQuarters = List.Max(Grouped[QuarterCount]),

    // Step 5: keep only active reps
    ActiveReps = Table.SelectRows(Grouped, each [QuarterCount] = MaxQuarters),
    // Step 6: re-join to original filtered data
    Result = Table.NestedJoin(
        Custom1,
        {"Name"},
        ActiveReps,
        {"Name"},
        "Active",
        JoinKind.Inner
    ),
    Expanded = Table.ExpandTableColumn(Result, "Active", {"QuarterCount"})
in
    Expanded

Calc Schema and Year can be adjusted at this step, 

Custom1 = Table.SelectRows(#"Changed Type", each [Calc Schema] = "main" and [Year] >= 2023)

if i'm using the above conditions i'll have below result at 'ActiveRep' step.