Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MJEnnis
Helper V
Helper V

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.

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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

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
Helper V
Helper V

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?

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?

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors