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...LEVEL1LEVEL2LEVEL3
1890...608
4567...046
4567...569

 

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.

 

  • 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

4 Replies

  • 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

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

    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

      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?