Max Value based upon two columns with distant filter

Hello CScottP 
Thank you for providing the dataset and the expected result table.

You can create the following DAX measure to calculate number of items:

Item Count = 
VAR _maxModOrd = MAX ( 'Table'[MOD_ORDER] )
VAR _actionValues = CALCULATE ( MAX ( 'Table'[ACTION] ), 'Table'[MOD_ORDER] = _maxModOrd )
VAR _items = CALCULATE ( SUM ( 'Table'[ITEMS] ), 'Table'[MOD_ORDER] = _maxModOrd )
VAR _trueItems = IF ( _actionValues = "DELETE", 0, _items ) 

// For Total Calculation
VAR _summarizedTable =
    SUMMARIZE (
        'Table',
        'Table'[TYPE],
        "max_mod_order", CALCULATE ( MAX ( 'Table'[MOD_ORDER] ) )
    )

VAR _extendedTotalTable =
    FILTER (
        ADDCOLUMNS (
            _summarizedTable,
            "action",
                LOOKUPVALUE (
                    'Table'[ACTION],
                    'Table'[TYPE], [TYPE],
                    'Table'[MOD_ORDER], [max_mod_order]
                ),
            "items",
                LOOKUPVALUE (
                    'Table'[ITEMS],
                    'Table'[TYPE], [TYPE],
                    'Table'[MOD_ORDER], [max_mod_order]
                )
        ),
        [action] <> "DELETE"
    )
VAR _totalItems =
    SUMX ( _extendedTotalTable, [items] )
RETURN
    IF ( ISINSCOPE ( 'Table'[TYPE] ), _trueItems, _totalItems )

Here is the screenshot of the result I got after applyint this DAX measure:

Here is the screenshot of the DAX measure:

I am also attaching the working Power BI file that I have used to solve your query, in case you need it.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Pround to be a Super User