Memory Limit Issue When Using REMOVEFILTERS with SUMX

[ttbt_dax_dev]

Hi everyone,

I followed this guide (https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/REMOVEFILTER-not-working-inside-SUMX...) 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!

[FBergamaschi]

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

FB

[ttbt_dax_dev]

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()
        )
)

[FBergamaschi · Accepted solution]

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

FB

[ttbt_dax_dev]

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....

[v-venuppu]

Hi @ttbt_dax_dev ,

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.

[lbendlin]

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).