topic Re: SELECTCOLUMNS / SUMMARIZE broken in calculated table Whereas it works in EVALUATE in SSMS in DAX Commands and Tips

SELECTCOLUMNS / SUMMARIZE broken in calculated table Whereas it works in EVALUATE in SSMS

Anonymous — Wed, 03 Jul 2019 14:58:55 GMT

I'm stumped, guys!

Both parts of this simple subtraction give the right result.

SQL Server version is 13.0.5337

For what it's worth I get the exact same result whether I use SUMMARIZECOLUMNS or ADDCOLUMNS and with or without a CALCULATE wrapping the measure.

Re: SELECTCOLUMNS / SUMMARIZE broken in calculated table Whereas it works in EVALUATE in SSMS

Stachu — Sun, 07 Jul 2019 06:56:59 GMT

Qhat's the syntax for [PROFIT_CUM]? that's the measure that's gets evaluated incorrectly, correct?

Re: SELECTCOLUMNS / SUMMARIZE broken in calculated table Whereas it works in EVALUATE in SSMS

Anonymous — Mon, 08 Jul 2019 12:28:38 GMT

It's a pretty simple one : it's the difference between two measures (see the screenshot between the two up there ^^ )

Both measures (INCOME_PLANNED_CUM and COST_CUM) evaluate correctly in the two 'environments'.

Re: SELECTCOLUMNS / SUMMARIZE broken in calculated table Whereas it works in EVALUATE in SSMS

Stachu — Mon, 08 Jul 2019 14:34:01 GMT

hmm, for some reason I thought that the [PROFIT_CUM_CALC] is based on [PROFIT_CUM] as they were both inconsistent between the pictures.

It's a nested calculation, so without details on what's happening in the nested measures there is no much to investigate

Re: SELECTCOLUMNS / SUMMARIZE broken in calculated table Whereas it works in EVALUATE in SSMS

Anonymous — Mon, 08 Jul 2019 14:54:59 GMT

The broken one is PROFIT_CUM.

PROFIT_CUM_CALC is where I recreate the measure using the aggregated 'base measures' instead of aggregating the PROFIT_CUM measure. Here's the code for the evaluate :

evaluate 
selectcolumns(
    SUMMARIZE(FACT_COST_ACTIVITY,FACT_COST_ACTIVITY[PROJECT NAME]),
    "PROJECT NAME",[PROJECT NAME],
    "PROFIT_CUM",[PROFIT_CUM],
    "COST_CUM",[COST_CUM],
    "INCOME_PLANNED_CUM",[INCOME_PLANNED_CUM],
    "PROFIT_CUM_CALC",[INCOME_PLANNED_CUM]-[COST_CUM]
)

Here's the full breakdown of the broken measure :

PROFIT_CUM := [INCOME_PLANNED_CUM]-[COST_CUM]

INCOME_PLANNED_CUM:= 
SUMX(
    VALUES(FACT_COST_ACTIVITY[ID_DIM_ACTIVITY]);
    VAR 
        LAST_DATES = 
        CALCULATE(
            MAX(FACT_COST_ACTIVITY[THE_DATE])
        )

RETURN
    CALCULATE(
        sum(FACT_COST_ACTIVITY[INCOME_PLANNED_CUM_COL]);
        DIM_DATE[THE_DATE] = LAST_DATES
    )
)

INCOME_PLANNED_CUM_COL :=
IF(
    FACT_COST_ACTIVITY[EXPENSE_ONLY_COL]; //BOOLEAN column in related dimension
    FACT_COST_ACTIVITY[COST_REAL_CUM]; //column
    FACT_COST_ACTIVITY[INCOME_BUDGET]  //column
    * 
    MIN(
        FACT_COST_ACTIVITY[QUANTITY_RATIO_CUM_COL];
        1
    )
)

QUANTITY_RATIO_CUM_COL :=
IF(
    FACT_COST_ACTIVITY[EXPENSE_ONLY_COL] = 1 && FACT_COST_ACTIVITY[QUANTITY_REAL_CUM] > 0;
    1;
    DIVIDE(
        FACT_COST_ACTIVITY[QUANTITY_REAL_CUM]; //column
        FACT_COST_ACTIVITY[QUANTITY_BUDGET_COL]; //column (in related table)
        0
    )
)

COST_CUM:=
SUMX(
    VALUES(FACT_COST_ACTIVITY[ID_DIM_ACTIVITY]);
    VAR 
        LAST_DATES = 
        CALCULATE(
            MAX(FACT_COST_ACTIVITY[THE_DATE])
        )

RETURN
    CALCULATE(
        sum(FACT_COST_ACTIVITY[COST_REAL_CUM]); //column
        DIM_DATE[THE_DATE] = LAST_DATES
    )
)
+ 
IF(not ISBLANK([QUANTITY_CUM]);0)