SUMX(Filter) Very Slow

[LillyLegenda]

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 🙂 

[AntrikshSharma]

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

[wdx223_Daniel]

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

[Payeras_BI]

Hi @LillyLegenda ,

Filtering only the column and not the entire table.

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

[mahoneypat]

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

[amitchandak]

@LillyLegenda , Try this

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

[LillyLegenda]

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