Need help with IF/Switch

[Robert1981]

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?

[Ahmedx]

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)

[Robert1981]

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..

[Ahmedx · Accepted solution]

try to split my measure into 4 type measures
Invoiced_QTY1, Invoiced_QTY2, Invoiced_QTY3, Invoiced_QTY4

Invoiced_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)

[Robert1981]

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

[Robert1981]

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

[PijushRoy]

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).