topic Re: Memory Limit Issue When Using REMOVEFILTERS with SUMX in DAX Commands and Tips

Memory Limit Issue When Using REMOVEFILTERS with SUMX

Anonymous — Mon, 23 Jun 2025 16:26:15 GMT

Hi everyone,

I followed this guide (https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/REMOVEFILTER-not-working-inside-SUMX/m-p/3670431) to use REMOVEFILTERS inside a SUMX expression, and it works for the most part. However, I'm now encountering a memory limit issue when applying filters on Customer Segment[1-10] — while other filters work fine.

My intention is to remove the customer segment filters only for PREARRAMOUNT. Below is the measure I'm using:

DOWNSELL = VAR __threshold = SELECTEDVALUE(REPORTING_THRESHOLD[THRESHOLD]) VAR __lvl = SELECTEDVALUE(REPORTING_LEVEL[LEVEL]) RETURN SUMX( SUMMARIZE( ARRDATA, ARRDATA[CUSTOMER], "begining_balance_wo_cs", CALCULATE( SUM(ARRDATA[PREARRAMOUNT]), REMOVEFILTERS( ARRDATA[CUSTOMERSEGMENT1], ARRDATA[CUSTOMERSEGMENT2], ARRDATA[CUSTOMERSEGMENT3] ) ), "revenue_change_product", CALCULATE( SUM(ARRDATA[ARRAMOUNT]) - SUM(ARRDATA[PREARRAMOUNT]), FILTER( ARRDATA, NOT ISBLANK( SWITCH( TRUE(), __threshold = 0, ARRDATA[CATEGORY_PRODUCT_0], __threshold = 1, ARRDATA[CATEGORY_PRODUCT_1], __threshold = 2, ARRDATA[CATEGORY_PRODUCT_2] BLANK() ) ) ) ), "revenue_customer_updown", CALCULATE( SUM(ARRDATA[ARRAMOUNT]) - SUM(ARRDATA[PREARRAMOUNT]), FILTER( ARRDATA, SWITCH( TRUE(), __threshold = 0, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_0] = "UPDOWN", __threshold = 1, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_1] = "UPDOWN", __threshold = 2, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_2] = "UPDOWN", FALSE() ) ) ) ), VAR __begining_balance_wo_cs = [begining_balance_wo_cs] VAR __revenue_change_product = [revenue_change_product] VAR __revenue_customer_updown = [revenue_customer_updown] VAR __revenue_product_updown = __revenue_customer_updown - __revenue_change_product RETURN SWITCH( TRUE(), __lvl = "Customer" && __revenue_customer_updown < 0, __revenue_customer_updown, __lvl = "Customer Product" && __revenue_product_updown < 0, __revenue_product_updown, BLANK() ) )

Has anyone experienced this issue or found a better way to achieve this without hitting the memory limit?

Thanks in advance for your help!

Re: Memory Limit Issue When Using REMOVEFILTERS with SUMX

lbendlin — Tue, 24 Jun 2025 00:52:04 GMT

Use DAX Studio to examine the query plan and study the cardinalities of the intermediate steps. Use variables to reduce said cardinalities earlier (filter as soon as possible).

Re: Memory Limit Issue When Using REMOVEFILTERS with SUMX

v-venuppu — Tue, 24 Jun 2025 06:13:46 GMT

Hi Anonymous ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you lbendlin for the prompt response.

To reduce memory usage and improve performance when using SUMX with complex filters:

1.Check where cardinality or memory spikes (especially from SUMMARIZE).

2.SUMMARIZE materializes large tables; VALUES + ADDCOLUMNS is more efficient.

3.Only remove filters from the necessary columns (not all segment fields).

4.Precompute SWITCH or complex logic using variables

-Avoid repeating expensive expressions inside iterators.

5.Apply filters before looping to reduce row volume in SUMX.

These steps help reduce memory consumption and avoid query timeouts in large or filtered datasets.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly.

Thank you.

Re: Memory Limit Issue When Using REMOVEFILTERS with SUMX

FBergamaschi — Tue, 24 Jun 2025 18:32:19 GMT

The best way to help is checking the data model, that would imply having the pbix

Apart from this I can just suggest the following code

DOWNSELL =

VAR __threshold = SELECTEDVALUE(REPORTING_THRESHOLD[THRESHOLD])

VAR __lvl = SELECTEDVALUE(REPORTING_LEVEL[LEVEL])

RETURN

SUMX(

ADDCOLUMNS (

VALUES ( ARRDATA[CUSTOMER] ),

"begining_balance_wo_cs",

CALCULATE(

SUM(ARRDATA[PREARRAMOUNT]),

REMOVEFILTERS(

ARRDATA[CUSTOMERSEGMENT1],

ARRDATA[CUSTOMERSEGMENT2],

ARRDATA[CUSTOMERSEGMENT3]

)

"revenue_change_product",

CALCULATE(

SUM(ARRDATA[ARRAMOUNT]) -

SUM(ARRDATA[PREARRAMOUNT]),

FILTER(
SUMMARIZE(

ARRDATA,
ARRDATA[CATEGORY_PRODUCT_0],

ARRDATA[CATEGORY_PRODUCT_1],

ARRDATA[CATEGORY_PRODUCT_2],
),

NOT ISBLANK(

SWITCH(

TRUE(),

__threshold = 0, ARRDATA[CATEGORY_PRODUCT_0],

__threshold = 1, ARRDATA[CATEGORY_PRODUCT_1],

__threshold = 2, ARRDATA[CATEGORY_PRODUCT_2]

BLANK()

)

"revenue_customer_updown",

CALCULATE(

SUM(ARRDATA[ARRAMOUNT]) -

SUM(ARRDATA[PREARRAMOUNT]),

FILTER(

SUMMARIZE(

ARRDATA,
ARRDATA[CATEGORY_UPDOWN_CUSTOMER_0],

ARRDATA[CATEGORY_UPDOWN_CUSTOMER_1],

ARRDATA[CATEGORY_UPDOWN_CUSTOMER_2],
),

SWITCH(

TRUE(),

__threshold = 0, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_0] = "UPDOWN",

__threshold = 1, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_1] = "UPDOWN",

__threshold = 2, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_2] = "UPDOWN",

FALSE()

)

VAR __begining_balance_wo_cs = [begining_balance_wo_cs]

VAR __revenue_change_product = [revenue_change_product]

VAR __revenue_customer_updown = [revenue_customer_updown]

VAR __revenue_product_updown = __revenue_customer_updown - __revenue_change_product

RETURN

SWITCH(

TRUE(),

__lvl = "Customer" && __revenue_customer_updown < 0, __revenue_customer_updown,

__lvl = "Customer Product" && __revenue_product_updown < 0, __revenue_product_updown,

BLANK()

)

Let me know!

If this helped pls consider giving kudos and/or mark as a solution

Best

Re: Memory Limit Issue When Using REMOVEFILTERS with SUMX

Anonymous — Wed, 25 Jun 2025 17:19:06 GMT

Thank you all for your solutions.

Your approach does improve performance, but it’s still slow. I tried applying v-venuppu 's suggestion to filter the dataset before looping in SUMX, but it didn’t help much. The measure works well with other slicers, but performance drops significantly when filtering by the Customer Segment 1, 2, or 3 slicer. I suspect there might be a conflict caused by using REMOVEFILTERS inside SUMX.

Do you have any further ideas or suggestions?

DOWNSELL = VAR __threshold = SELECTEDVALUE(REPORTING_THRESHOLD[THRESHOLD]) VAR __lvl = SELECTEDVALUE(REPORTING_LEVEL[LEVEL]) var __filter_dataset = FILTER(ARRDATA, SWITCH( TRUE(), __threshold = 0, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_0] = "UPDOWN", __threshold = 1, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_1] = "UPDOWN", __threshold = 2, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_2] = "UPDOWN", FALSE() ) ) RETURN SUMX( ADDCOLUMNS ( VALUES ( ARRDATA[CUSTOMER] ), "begining_balance_wo_cs", CALCULATE( SUM(ARRDATA[PREARRAMOUNT]), REMOVEFILTERS( ARRDATA[CUSTOMERSEGMENT1], ARRDATA[CUSTOMERSEGMENT2], ARRDATA[CUSTOMERSEGMENT3], ), __filter_dataset ), "revenue_change_product", CALCULATE( SUM(ARRDATA[ARRAMOUNT]) - SUM(ARRDATA[PREARRAMOUNT]), FILTER( SUMMARIZE( __filter_dataset, ARRDATA[CATEGORY_PRODUCT_0], ARRDATA[CATEGORY_PRODUCT_1], ARRDATA[CATEGORY_PRODUCT_2], ), NOT ISBLANK( SWITCH( TRUE(), __threshold = 0, ARRDATA[CATEGORY_PRODUCT_0], __threshold = 1, ARRDATA[CATEGORY_PRODUCT_1], __threshold = 2, ARRDATA[CATEGORY_PRODUCT_2], BLANK() ) ) ) ), "revenue_customer_updown", CALCULATE( SUM(ARRDATA[ARRAMOUNT]) - SUM(ARRDATA[PREARRAMOUNT]), SUMMARIZE( __filter_dataset, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_0], ARRDATA[CATEGORY_UPDOWN_CUSTOMER_1], ARRDATA[CATEGORY_UPDOWN_CUSTOMER_2], ) ) ), VAR __begining_balance_wo_cs = [begining_balance_wo_cs] VAR __revenue_change_product = [revenue_change_product] VAR __revenue_customer_updown = [revenue_customer_updown] VAR __revenue_product_updown = __revenue_customer_updown - __revenue_change_product RETURN SWITCH( TRUE(), __lvl = "Customer" && __revenue_customer_updown < 0, __revenue_customer_updown, __lvl = "Customer Product" && __revenue_product_updown < 0, __revenue_product_updown, BLANK() ) )

Re: Memory Limit Issue When Using REMOVEFILTERS with SUMX

FBergamaschi — Wed, 25 Jun 2025 18:02:13 GMT

Yes, kill the SWITCH statemens like follows (I start again from my code as I have seen that you have added new filters here and there mixing the different solutions offered)

DOWNSELL =

VAR __threshold = SELECTEDVALUE(REPORTING_THRESHOLD[THRESHOLD])

VAR __lvl = SELECTEDVALUE(REPORTING_LEVEL[LEVEL])

RETURN

SUMX(

ADDCOLUMNS (

VALUES ( ARRDATA[CUSTOMER] ),

"begining_balance_wo_cs",

CALCULATE(

SUM(ARRDATA[PREARRAMOUNT]),

REMOVEFILTERS(

ARRDATA[CUSTOMERSEGMENT1],

ARRDATA[CUSTOMERSEGMENT2],

ARRDATA[CUSTOMERSEGMENT3]

)

"revenue_change_product",

CALCULATE(

SUM(ARRDATA[ARRAMOUNT]) -

SUM(ARRDATA[PREARRAMOUNT]),

FILTER(
SUMMARIZE(

ARRDATA,
ARRDATA[CATEGORY_PRODUCT_0],

ARRDATA[CATEGORY_PRODUCT_1],

ARRDATA[CATEGORY_PRODUCT_2],
),

NOT ISBLANK(

IF(

__threshold = 0, ARRDATA[CATEGORY_PRODUCT_0],
IF(

__threshold = 1, ARRDATA[CATEGORY_PRODUCT_1],
IF(

__threshold = 2, ARRDATA[CATEGORY_PRODUCT_2]

)
)

)

"revenue_customer_updown",

CALCULATE(

SUM(ARRDATA[ARRAMOUNT]) -

SUM(ARRDATA[PREARRAMOUNT]),

FILTER(

SUMMARIZE(

ARRDATA,
ARRDATA[CATEGORY_UPDOWN_CUSTOMER_0],

ARRDATA[CATEGORY_UPDOWN_CUSTOMER_1],

ARRDATA[CATEGORY_UPDOWN_CUSTOMER_2],
),

IF(

__threshold = 0, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_0] = "UPDOWN",
IF(

__threshold = 1, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_1] = "UPDOWN",
IF(

__threshold = 2, ARRDATA[CATEGORY_UPDOWN_CUSTOMER_2] = "UPDOWN",

                        FALSE()
                                           )
                            )

)

VAR __begining_balance_wo_cs = [begining_balance_wo_cs]

VAR __revenue_change_product = [revenue_change_product]

VAR __revenue_customer_updown = [revenue_customer_updown]

VAR __revenue_product_updown = __revenue_customer_updown - __revenue_change_product

RETURN

IF(

__lvl = "Customer" && __revenue_customer_updown < 0, __revenue_customer_updown,
IF(

__lvl = "Customer Product" && __revenue_product_updown < 0, __revenue_product_updown

)

If still this is not enough I would need the data model in my hands

Please give kudos or mark as a solution if this helped solving

best

Re: Memory Limit Issue When Using REMOVEFILTERS with SUMX

Anonymous — Thu, 26 Jun 2025 18:11:52 GMT

Sorry, I can't upload a file to the cloud due to security restrictions.

Removing SWITCH has improved performance.

Now it's much better — most slicers respond quickly (around 5 seconds), but the Customer Segment filters are still relatively slow (around >10seconds). I suspect this is because several measures also use REMOVEFILTERS on the customer segment fields, which could explain why filtering by Customer Segment performs worse than the others.

Should I replace SWITCH in all measures? There are 41 measures in total, and they share a similar structure — each depends on 10 category columns based on the selected threshold.

My semantic model has only a single table that contains all the necessary information, and the visual displays revenue changes by month

One of the other slicers is related to categories and uses REMOVEFILTERS on Customer Segment:

NEW CUSTOMER =

VAR __threshold = SELECTEDVALUE(EXCELWORKBOOK_THRESHOLD[THRESHOLD])

RETURN

CALCULATE(

[REVENUE_CHANGE_CUSTOMER],

FILTER(

ARRDATA,

SWITCH(

TRUE(),

__threshold = 0, ARRDATA[CATEGORY_CUSTOMER_0] = "NEW CUSTOMER",

__threshold = 1, ARRDATA[CATEGORY_CUSTOMER_1] = "NEW CUSTOMER",

__threshold = 2, ARRDATA[CATEGORY_CUSTOMER_2] = "NEW CUSTOMER",

__threshold = 3, ARRDATA[CATEGORY_CUSTOMER_3] = "NEW CUSTOMER",

__threshold = 6, ARRDATA[CATEGORY_CUSTOMER_6] = "NEW CUSTOMER",

__threshold = 9, ARRDATA[CATEGORY_CUSTOMER_9] = "NEW CUSTOMER",

__threshold = 12, ARRDATA[CATEGORY_CUSTOMER_12] = "NEW CUSTOMER",

__threshold = 18, ARRDATA[CATEGORY_CUSTOMER_18] = "NEW CUSTOMER",

__threshold = 24, ARRDATA[CATEGORY_CUSTOMER_24] = "NEW CUSTOMER",

__threshold = 10000, ARRDATA[CATEGORY_CUSTOMER_10000] = "NEW CUSTOMER",

FALSE()

)

REVENUE_CHANGE_CUSTOMER =

VAR __revenue_change_customer_wo_cs = [__revenue_change_customer_wo_cs]

VAR __ending = IF(ISBLANK(SUM(ARRDATA[ARRAMOUNT])), -1, 1)

RETURN IF(__ending=1 && __revenue_change_customer_wo_cs<>0, __revenue_change_customer_wo_cs , BLANK())

__revenue_change_customer_wo_cs = [ENDING_BALANCE] - [__beginning_balance_wo_cs]

__beginning_balance_wo_cs =

CALCULATE(

SUM(ARRDATA[PREARRAMOUNT]),

REMOVEFILTERS(

ARRDATA[CUSTOMERSEGMENT1],

ARRDATA[CUSTOMERSEGMENT2],

ARRDATA[CUSTOMERSEGMENT3],

ARRDATA[CUSTOMERSEGMENT4],

ARRDATA[CUSTOMERSEGMENT5],

ARRDATA[CUSTOMERSEGMENT6],

ARRDATA[CUSTOMERSEGMENT7],

ARRDATA[CUSTOMERSEGMENT8],

ARRDATA[CUSTOMERSEGMENT9],

ARRDATA[CUSTOMERSEGMENT10]

)

Any advice on my model? Everything works well now, except when filtering by Customer Segment, it's still slow....