topic Re: Filtering by max value within calculated table in DAX Commands and Tips

Filtering by max value within calculated table

MJEnnis — Wed, 19 Jan 2022 08:13:17 GMT

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.

Re: Filtering by max value within calculated table

MJEnnis — Wed, 19 Jan 2022 14:19:03 GMT

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?

Re: Filtering by max value within calculated table

MJEnnis — Wed, 19 Jan 2022 14:36:24 GMT

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?

Re: Filtering by max value within calculated table

AlexisOlson — Wed, 19 Jan 2022 16:52:42 GMT

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

Re: Filtering by max value within calculated table

MJEnnis — Wed, 19 Jan 2022 17:26:49 GMT

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.