Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |