Forum Discussion
Solved
Need help with IF/Switch
Hi,
Below is a screenshot from my report.
I have an extract from SAP. In the report it gives me all the possible billing quantities. I need to find the right quantities, based on the Sales Unit. In the formula I set up the relationships between the sales unit and the quantity. However, PBI tells me that the formula is too difficult. I couldn't get it to work with Switch either. How can I get the correct billed quantities in 1 single column?
try to split my measure into 4 type measures
Invoiced_QTY1, Invoiced_QTY2, Invoiced_QTY3, Invoiced_QTY4Invoiced_QTY1 = VAR _SalesUnit = [SalesUnit] VAR _t1 = _SalesUnit="KG" VAR _t2 = _SalesUnit in {"LB", "/LB"} VAR _t3 = _SalesUnit ="BX" VAR St_UOM = SWITCH(TRUE(), _t1,[InvQtyKG], _t2 , [InvQtyE], _t3,[InvQtyBX], _ RETURN St_UOM ---------------- Invoiced_QTY2 = VAR _SalesUnit = [SalesUnit] VAR _t4 = _SalesUnit ="CT" VAR _t5 = _SalesUnit ="SM" VAR _t6 = _SalesUnit ="LT" VAR St_UOM = SWITCH(TRUE(), _t4,[InvQtyCT], _t5,[InvQtySM], _t6,[InvQtyLTR], RETURN St_UOM ----------------- Invoiced_QTY3 = VAR _SalesUnit = [SalesUnit] VAR _t7 = _SalesUnit ="PF" VAR _t8 = _SalesUnit ="RL" VAR _t9 = _SalesUnit in {"DR", "KT","EA"} VAR REVENUE = [InvGrossAnrtDocCurr] VAR St_UOM = SWITCH(TRUE(), _t7,[InvQtyPF], _t8,[InvQtyRL], _t9,[InvQtyEA], RETURN St_UOM ------------------ Invoiced_QTY4 = VAR _SalesUnit = [SalesUnit] VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"} VAR St_UOM = SWITCH(TRUE(), _t10,[InvQtyS]) RETURN St_UOM ------------------------- Invoiced_QTY_Final = VAR REVENUE = [InvGrossAnrtDocCurr] RETURN DIVIDE(REVENUE,[Invoiced_QTY1]+[Invoiced_QTY2]+[Invoiced_QTY3]+[Invoiced_QTY4],0)
6 Replies
Hi Robert1981
Please create a sample PBIX with similar data but data source should be Excel or insert data in Power Queyr. and share here the Google Drive link (after removing all sensitive data).I can't share a file. Below is a sample set of the data
BillingDoc BillingItem PostingDate PricingDate InvGrossAmtDocCurr InvNetValDocCurr InvQtyTB InvQtySM InvQtyS InvQtyRL InvQtyPF InvQtyLTR InvQtyKG InvQtyKD InvQtyGA InvQtyEA InvQtyE InvQtyCT InvQtyBX InvQtyB SalesUnit SalesUnit_txt 7.71E+09 10 ######## ######## 1715.2 1715.2 0 22.514 10 0 0.062 0 24.93 0 0 0 54.912 0 0.062 24.93 SH Sheet 7.61E+09 10 ######## ######## 6141.61 6455.23 0 13.75 25 0 0 0 23.525 0 0 25 25 0 25 25 EA Each 7.73E+09 10 ######## ######## 39933.87 39933.87 0 5305.5 50 50 0 0 366.08 0 0 0 5305.5 0 0 5305.5 RL Roll 7.72E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 KG Kilogram 7.61E+09 10 ######## ######## 229720 229720 0 0 2809 0 0 0 11162.97 0 2809 0 24588.03 0 0 11162.97 GA Gallon, US 7.61E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BX Box 7.62E+09 40 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 /LB per pound 7.05E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 YD Yard 7.05E+09 10 ######## ######## 840 840 0 0 100 0 0.018 0 9.978 0 0 2 2 2 2 2 UN Unit 7.6E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SM Sq Meter 7.61E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 LT Liter 7.61E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 GR Gram 7.62E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 DR Drum 7.62E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 LB Pound 7.62E+09 90 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 CT Carton 7.62E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 FT Foot 7.62E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 PF Pallet 7.62E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SY Sq yard 7.73E+09 20 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 LM Linear m 7.78E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 KT Kit 7.78E+09 10 ######## ######## 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BG Bag BillingDoc BillingItem PostingDate PricingDate InvGrossAmtDocCurr InvNetValDocCurr InvQtyTB InvQtySM InvQtyS InvQtyRL InvQtyPF InvQtyLTR InvQtyKG InvQtyKD InvQtyGA InvQtyEA InvQtyE InvQtyCT InvQtyBX InvQtyB SalesUnit SalesUnit_txt 7.71E+09 10 1/16/2023 9/16/2022 1715.2 1715.2 0 22.514 10 0 0.062 0 24.93 0 0 0 54.912 0 0.062 24.93 SH Sheet 7.61E+09 10 1/16/2023 12/9/2022 6141.61 6455.23 0 13.75 25 0 0 0 23.525 0 0 25 25 0 25 25 EA Each 7.73E+09 10 1/16/2023 12/26/2022 39933.87 39933.87 0 5305.5 50 50 0 0 366.08 0 0 0 5305.5 0 0 5305.5 RL Roll 7.72E+09 10 1/16/2023 1/13/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 KG Kilogram 7.61E+09 10 1/15/2023 1/15/2023 229720 229720 0 0 2809 0 0 0 11162.97 0 2809 0 24588.03 0 0 11162.97 GA Gallon, US 7.61E+09 10 1/15/2023 1/15/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BX Box 7.62E+09 40 1/15/2023 1/15/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 /LB per pound 7.05E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 YD Yard 7.05E+09 10 1/16/2023 1/16/2023 840 840 0 0 100 0 0.018 0 9.978 0 0 2 2 2 2 2 UN Unit 7.6E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SM Sq Meter 7.61E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 LT Liter 7.61E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 GR Gram 7.62E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 DR Drum 7.62E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 LB Pound 7.62E+09 90 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 CT Carton 7.62E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 FT Foot 7.62E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 PF Pallet 7.62E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SY Sq yard 7.73E+09 20 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 LM Linear m 7.78E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 KT Kit 7.78E+09 10 1/16/2023 1/16/2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BG Bag
pls try this
Invoiced_QTY = VAR _SalesUnit = [SalesUnit] VAR _t1 = _SalesUnit="KG" VAR _t2 = _SalesUnit in {"LB", "/LB"} VAR _t3 = _SalesUnit ="BX" VAR _t4 = _SalesUnit ="CT" VAR _t5 = _SalesUnit ="SM" VAR _t6 = _SalesUnit ="LT" VAR _t7 = _SalesUnit ="PF" VAR _t8 = _SalesUnit ="RL" VAR _t9 = _SalesUnit in {"DR", "KT","EA"} VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"} VAR REVENUE = [InvGrossAnrtDocCurr] VAR St_UOM = SWITCH(TRUE(), _t1,[InvQtyKG], _t2 , [InvQtyE], _t3,[InvQtyBX], _t4,[InvQtyCT], _t5,[InvQtySM], _t6,[InvQtyLTR], _t7,[InvQtyPF], _t8,[InvQtyRL], _t9,[InvQtyEA], _t10,[InvQtyS]) RETURN DIVIDE(REVENUE,St_UOM,0)Thank you,
Just tried it and I get the same error message:
OLE or ODBC error: We couldn't fold the expression to the data source. Please try a simpler expression..try to split my measure into 4 type measures
Invoiced_QTY1, Invoiced_QTY2, Invoiced_QTY3, Invoiced_QTY4Invoiced_QTY1 = VAR _SalesUnit = [SalesUnit] VAR _t1 = _SalesUnit="KG" VAR _t2 = _SalesUnit in {"LB", "/LB"} VAR _t3 = _SalesUnit ="BX" VAR St_UOM = SWITCH(TRUE(), _t1,[InvQtyKG], _t2 , [InvQtyE], _t3,[InvQtyBX], _ RETURN St_UOM ---------------- Invoiced_QTY2 = VAR _SalesUnit = [SalesUnit] VAR _t4 = _SalesUnit ="CT" VAR _t5 = _SalesUnit ="SM" VAR _t6 = _SalesUnit ="LT" VAR St_UOM = SWITCH(TRUE(), _t4,[InvQtyCT], _t5,[InvQtySM], _t6,[InvQtyLTR], RETURN St_UOM ----------------- Invoiced_QTY3 = VAR _SalesUnit = [SalesUnit] VAR _t7 = _SalesUnit ="PF" VAR _t8 = _SalesUnit ="RL" VAR _t9 = _SalesUnit in {"DR", "KT","EA"} VAR REVENUE = [InvGrossAnrtDocCurr] VAR St_UOM = SWITCH(TRUE(), _t7,[InvQtyPF], _t8,[InvQtyRL], _t9,[InvQtyEA], RETURN St_UOM ------------------ Invoiced_QTY4 = VAR _SalesUnit = [SalesUnit] VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"} VAR St_UOM = SWITCH(TRUE(), _t10,[InvQtyS]) RETURN St_UOM ------------------------- Invoiced_QTY_Final = VAR REVENUE = [InvGrossAnrtDocCurr] RETURN DIVIDE(REVENUE,[Invoiced_QTY1]+[Invoiced_QTY2]+[Invoiced_QTY3]+[Invoiced_QTY4],0)
