Help with optimizing DAX query

Anonymous · ‎01-10-2019

I have this measure (COST_PROJECTION) that takes ages to load and I'm pretty sure there are some easy optimizations to be done.

Basically, for each RESOURCE_CATEGORY (the finest grain), on every day, if there are quantities (QUANTITY_CUM) and costs (COST_CUM) I want to calculate the COST_PROJECTION_REAL, otherwise the COST_PROJECTION_ESTIMATED. Then those values are summed up (resource category < activity < project).

Here are the formulas :

COST_PROJECTION:=
SUMX (
    ALLSELECTED ( FACT_COST_RESOURCE_CATEGORY[ID_DIM_RESOURCE_CATEGORY] ) ;
    IF ( 
        ( [QUANTITY_CUM] > 0 ) && not ISBLANK (  [COST_CUM]) ;
        [UNIT_COST_CUM] * [QUANTITY_PLANNED_PROJECT]; /*COST_PROJECTION_REAL*/
        [COST_CUM]  + [COST_PLANNED_PROJECT] /*COST_PROJECTION_ESTIMATED*/
    )
)

QUANTITY_CUM :=
IF(
    min(DIM_DATE[THE_DATE]) <=  CALCULATE ( MAX ( FACT_COST_RESOURCE_CATEGORY[THE_DATE] ); ALL ( FACT_COST_RESOURCE_CATEGORY ) ) +1;
    SUMX(
        FILTER(
            ALL(DIM_DATE[THE_DATE]);
            DIM_DATE[THE_DATE] <= max(DIM_DATE[THE_DATE])
        );
        [QUANTITY]
    )
)

QUANTITY:=
SUMX(
    GROUPBY(
        FACT_COST_RESOURCE_CATEGORY;
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_ACTIVITY];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_PROJECT];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_DATE];
        "QTY";
        MINX(CURRENTGROUP();[QUANTITY_RES_CAT_PERIOD_COL])
    );
    [QTY]
)


UNIT_COST_CUM :=
(DIVIDE([COST_CUM]; [QUANTITY_CUM];0)


COST_CUM :=
IF(
    min(DIM_DATE[THE_DATE]) <=  CALCULATE ( MAX ( FACT_COST_RESOURCE_CATEGORY[THE_DATE] ); ALL ( FACT_COST_RESOURCE_CATEGORY ) ) +1;
    SUMX(
        FILTER(
            ALL(DIM_DATE[THE_DATE]);
            DIM_DATE[THE_DATE] <= max(DIM_DATE[THE_DATE])
        );
        FIRSTNONBLANK(FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL];1)
    )
)

QUANTITY_PLANNED_PROJECT:=
SUMX(
    SUMMARIZE(
        FACT_COST_RESOURCE_CATEGORY;
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_ACTIVITY];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_PROJECT]
    );
    FIRSTNONBLANK(FACT_COST_RESOURCE_CATEGORY[QUANTITY_BUDGET_COL];1)
)

COST_PLANNED_PROJECT:=
SUMX(
    SUMMARIZE(
        FACT_COST_RESOURCE_CATEGORY;
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_PROJECT];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_ACTIVITY];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_RESOURCE_CATEGORY];
        "COST PLANNED PROJECT";
        FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_EQUIPMENT_BUDGET];1)
        +
        FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_LABOR_BUDGET];1)
        +
        FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_UNIT_BUDGET];1)
    );
    [COST PLANNED PROJECT]
)

NOTE that quantities are semi-additives. They're at the activity level.

Anonymous · ‎01-10-2019

HI @Anonymous,

I found you are lots of iterators calculation functions in your formulas, if your table contains huge amount of records, it obviously will cause the performance issue.

In addition, when you combo use if statement and iterators functions will increase looping amount.(e.g COST_CUM measure. iterator functions will loop all rows in expression table. It means functions will loop multiple times when you use it in if statement: row count which suitable for the conditionals * dimdate table row count)

Reference links:

Optimizing nested iterators in DAX

Optimizing DAX with cardinality estimation: computing working days

Regards,

Xiaoxin Sheng