Forum Discussion
Memory Limit Issue When Using REMOVEFILTERS with SUMX
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
Community SupportHi 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.