Forum Discussion

MJEnnis's avatar
MJEnnis
Resolver III
4 years ago
Solved

Filtering by max value within calculated table

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 ... LEVE...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
MJEnnis
Resolver III
4 years ago

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's avatar
MJEnnis
Resolver III
4 years ago

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?