Forum Discussion

Anonymous

Not applicable

1 year ago

Solved

Memory Limit Issue When Using REMOVEFILTERS with SUMX

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!

FBergamaschi
1 year ago
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

6 Replies

lbendlin
Super User
1 year ago
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).
v-venuppu
Community Support
1 year ago
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.
FBergamaschi
Super User
1 year ago
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
- Anonymous
  Not applicable
  1 year ago
  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
    Super User
    1 year ago
    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

Forum Discussion

Memory Limit Issue When Using REMOVEFILTERS with SUMX

6 Replies

Related content

Semantic model Refresh memory limit error

Semantic Model Refresh - Memory limit

Query Memory Limit (%) bypassed

Memory limit issue for Direct Lake reports

Increase per query memory limit