Issue on variable within measurement - No result, No error

[novicenovice]

Dear all,

I have an issue on my calculation of my variable IDDLP in Fig 1 which determinate the max date and used directly in  my measurement fig3. The calculation run run whithout providing the result and without showing any error message but when I use the code in fig2 the calculation return the result quickly.

on Fig1 the issue remains even if the condition of the switch return 1

Fig1

           VAR IDDTLP= SWITCH(IDVSTYPE
                ;1;IDDT
                ;2;IDDT
                ;3;IDDT
                ;4;IDDT
                ;5;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;V_DATA_PORTFOLIO[DATE_BASE_PRICE]<IDDT&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;6;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;7;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;8;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])<=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;IDDT
    )      

fig 2

         VAR IDDTLP= IDDT

fig 3

BASE_VOLUME_PRICE_INDEX_REF = 
VAR IDINDICATOR = MIN(SELECT_VOLUME[VOLUME_ID])  var IDPERIODTYPE = MIN(SELECT_IND_TYPE[ID_SELECT_IND_TYPE])
VAR IDPERIODICITY = MIN(PERIOD_TYPE[PERIOD_TYPE_ID])
VAR IDDT = [DEFAULT_REF_DATE]
VAR IDDTYEAR = YEAR(IDDT)
VAR IDDTBEGINNING = [DEFAULT_REF_DATE_BEGINNING]
VAR IDVSTYPE = [ID_VS_TYPE_DEFAULT]

VAR IDRESULT = CALCULATE(SUMX(
    GROUPBY(SELECTCOLUMNS(V_DATA_PORTFOLIO;"CUST";RELATED(TR_CUSTOMER[CUST_BASE_PRICE]);"SKU";RELATED(TR_MATERIAL[SKU_BASE_PRICE]))
    ;[CUST];[SKU]);
    VAR IDCUST = [CUST]
    VAR IDSKU = [SKU]
    VAR IDSKUSPECIALVOLUME = LOOKUPVALUE(T_MATERIAL_PI_IND_SOURCE[IND_PI];T_MATERIAL_PI_IND_SOURCE[MATERIAL];IDSKU)

           VAR IDDTLP= SWITCH(IDVSTYPE
                ;1;IDDT
                ;2;IDDT
                ;3;IDDT
                ;4;IDDT
                ;5;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;V_DATA_PORTFOLIO[DATE_BASE_PRICE]<IDDT&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;6;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;7;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;8;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])<=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;IDDT
    )            

    VAR IDDTLPBEGINNING = if(IDVSTYPE in {5;6;7;8};DATE(YEAR(IDDTLP);MONTH(IDDTLP);1);IDDTBEGINNING)


    VAR IDVOLUME = SWITCH(IDSKUSPECIALVOLUME
                        ;"M2"; CALCULATE([Portf M2 - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                        ;"M3";CALCULATE([Portf M3 - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                        ;"QTY";CALCULATE([Portf QTY - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                        ;CALCULATE([Portf Tons - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                       )

RETURN IDVOLUME )))

return IDRESULT

Thanks for your help

Novice

[v-xicai]

Hi @novicenovice ,

You may need to sort out the formula's logic, there are too much VAR statements nested in the SUMX and GROUPBY function. You can use VAR statement one by one, the next VAR statement can refer to the last VAR statement, then use a RETURN statement to return the final result.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[novicenovice]

Hello Amy,

I'm sorry but I don't understand your recommendation as my english is quite basic.

Could you relaborate it by providing an example ?

Thanks

Novice