topic Re: SUMX(Filter) Very Slow in DAX Commands and Tips

SUMX(Filter) Very Slow

LillyLegenda — Tue, 02 Feb 2021 08:54:39 GMT

Hi, I have a big dataset with 1,8 million rows which is in general performing very good but one formula is very slow.

Maybe you have an idea to make it faster.

Volume AutoCreated =
SUMX(
FILTER(
PurchaseOrders,
PurchaseOrders[AutoCreationFlag] = "AutoCreated"
),
PurchaseOrders[ValuePurchasedUSD]
)

Thank you very much for your help 🙂

Re: SUMX(Filter) Very Slow

amitchandak — Tue, 02 Feb 2021 10:36:12 GMT

LillyLegenda , Try this

calculate(sum(PurchaseOrders[ValuePurchasedUSD]),FILTER(
PurchaseOrders,
PurchaseOrders[AutoCreationFlag] = "AutoCreated"
))

Re: SUMX(Filter) Very Slow

LillyLegenda — Tue, 02 Feb 2021 11:16:36 GMT

Thanks, but this is taking the same time maybe longer to load

Re: SUMX(Filter) Very Slow

mahoneypat — Tue, 02 Feb 2021 12:53:30 GMT

Please confirm that PurchaseOrders[ValuePurchaseUSD] is a column in your table, and not a measure. If a measure, please provide the expression for it. If a column, that is a straight forward expression and I am surprised that it is slow. Can you say more about your model (relationships with other tables, visuals on your report page, etc.)?

Pat

Re: SUMX(Filter) Very Slow

Payeras_BI — Tue, 02 Feb 2021 13:24:40 GMT

Hi LillyLegenda ,

Filtering only the column and not the entire table.

Volume AutoCreated = CALCULATE ( SUM ( PurchaseOrders[ValuePurchasedUSD] ), FILTER ( ALL ( PurchaseOrders[AutoCreationFlag] ), PurchaseOrders[AutoCreationFlag] = "AutoCreated" ) )

Re: SUMX(Filter) Very Slow

wdx223_Daniel — Tue, 02 Feb 2021 13:05:08 GMT

calculate(sum(PurchaseOrders[ValuePurchasedUSD]),PurchaseOrders[AutoCreationFlag] = "AutoCreated",ALL(PurchaseOrders))

Re: SUMX(Filter) Very Slow

AntrikshSharma — Tue, 02 Feb 2021 17:29:00 GMT

LillyLegenda On how to ask a technical question, if you really want an answer - SQLBI