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
Super UserThe 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
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
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
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])RETURNCALCULATE([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....