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

AlexisOlson's avatar
AlexisOlson
Super User
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

    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.