Query has exceeded the available resources.

[mann0321]

I'm having this error at my table after using multiple SUMX in a dax. Is there any alternate solution besides simplifying the query, reducing data volume, and optimizing the data model? hoping to fix it in an alternate Dax solution

Cal_L2_amount_sumx =

VAR cal =

    SUMX(

        dim_cost_type,

        SUMX(

            FILTER(

                dim_cost_center,

                dim_cost_center[Level1] IN VALUES(dim_market[Market])

            ),

            SUMX(

                dim_brand,

                SUMX(

                    vc_dim_wrap,

                    SUMX(

                        dim_date,

                        [sum_amount]

                    )

                )

            )

        )

    )

RETURN

    cal

[danextian]

This measure is computationally expensive, primarily due to its heavy reliance on multiple nested SUMX functions and iterative calculations over potentially large tables.

[bhanu_gautam]

@mann0321 Try using  SUMMARIZE to pre-aggregate the data at different levels before performing the final aggregation

Cal_L2_amount_sumx =
VAR summarizedTable =
SUMMARIZE(
dim_cost_center,
dim_cost_center[Level1],
dim_market[Market],
dim_cost_type[CostType],
dim_brand[Brand],
vc_dim_wrap[Wrap],
dim_date[Date],
"SumAmount", SUM([sum_amount])
)
VAR filteredTable =
FILTER(
summarizedTable,
dim_cost_center[Level1] IN VALUES(dim_market[Market])
)
RETURN
SUMX(filteredTable, [SumAmount])