Filtering by max value within calculated table

[MJEnnis]

I have a calculated table that works something like this:

Var TAB1 = SELECTCOLUMNS(...

Var TAB2 = ADDCOLUMNS(...

RETURN TAB2

The resulting table is something like this:

ID	...	LEVEL1	LEVEL2	LEVEL3
1890	...	6	0	8
4567	...	0	4	6
4567	...	5	6	9

I need to filter out the the lower summed level rows for each duplicated ID. (Never more than two occurences of each ID. And only 20 duplicates in a table with over 23000 rows. But I urgently need to remove these duplicated entries.)

The easiest solution to me seems to be to create a LEVEL_SUM column and then to filter out the rows where the the sum does not equal the max value for each ID. 

This of course works fine: 

Var TAB3 = ADDCOLUMNS(TAB2, "LEVEL_SUM", [LEVEL 1] + [LEVEL 2] + [LEVEL 3])

However, since this is all within the same code for a calculated table, the following will not work:

Var TEMPID = [ID]     # <= PROBLEM IS HERE! This is not yet a column, but a column expression.

As a result, none of the rest will work:

Var TAB4 = ADDCOLUMNS(TAB3, "MAX_LEVEL", Maxx(FILTER(TAB3, [ID] = TEMPID), [LEVEL_SUM]))

Var TAB5 = FILTER(TAB4, [MAX_LEVEL] = [LEVEL_SUM])

RETURN TAB5

Does anyone have any crafty tips to do this without creating a separate calculated table and without Power Query? This would probably work as a calculated column instead, but the problem is that I have to "wrangle" the data a lot more after I filter these rows out.

[AlexisOlson · Accepted solution]

I think something like this should work:

CalculatedTable =
VAR TAB1 = SELECTCOLUMNS ( [...] )
VAR TAB2 = ADDCOLUMNS ( TAB1, [...] )
VAR TAB3 = ADDCOLUMNS ( TAB2, "LEVEL_SUM", [LEVEL 1] + [LEVEL 2] + [LEVEL 3] )
RETURN
    FILTER (
        TAB3,
        [LEVEL_SUM]
            = MAXX ( FILTER ( TAB3, [ID] = EARLIER ( [ID] ) ), [LEVEL_SUM] )
    )

But I haven't checked it against anything

[MJEnnis]

Works like a charm! Thanks!

I just added it as a new variable, instead of as a filter on the resulting table, as I still have some more steps to take before the table is ready. 

[MJEnnis]

No takers? Is it not possible? Do I have to do a calculated column and then create a new calculated table applying the desired filter?

[MJEnnis]

This works perfectly as a calculated column: 

Max Levels =

Var TEMPID = [ID]

Var MAX_LEVELS = Maxx(FILTER(TAB3, [ID] = TEMPID), [LEVEL_SUM])

RETURN MAX_LEVELS

But then I have to create a new calculated table that filters for Level Sum = Max Level. I already have more tables than I can manage in my model.

Where are the real gurus today?