Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Robert1981
Helper II
Helper II

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?

 

PBI_error.JPG

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

View solution in original post

6 REPLIES 6
Ahmedx
Super User
Super User

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_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
Helper II
Helper II

I can't share a file. Below is a sample set of the data

 

BillingDocBillingItemPostingDatePricingDateInvGrossAmtDocCurrInvNetValDocCurrInvQtyTBInvQtySMInvQtySInvQtyRLInvQtyPFInvQtyLTRInvQtyKGInvQtyKDInvQtyGAInvQtyEAInvQtyEInvQtyCTInvQtyBXInvQtyBSalesUnitSalesUnit_txt
7.71E+0910################1715.21715.2022.5141000.062024.9300054.91200.06224.93SHSheet
7.61E+0910################6141.616455.23013.752500023.52500252502525EAEach
7.73E+0910################39933.8739933.8705305.5505000366.080005305.5005305.5RLRoll
7.72E+0910################0000000000000000KGKilogram
7.61E+0910################22972022972000280900011162.9702809024588.030011162.97GAGallon, US
7.61E+0910################0000000000000000BXBox
7.62E+0940################0000000000000000/LBper pound
7.05E+0910################0000000000000000YDYard
7.05E+0910################8408400010000.01809.9780022222UNUnit
7.6E+0910################0000000000000000SMSq Meter
7.61E+0910################0000000000000000LTLiter
7.61E+0910################0000000000000000GRGram
7.62E+0910################0000000000000000DRDrum
7.62E+0910################0000000000000000LBPound
7.62E+0990################0000000000000000CTCarton
7.62E+0910################0000000000000000FTFoot
7.62E+0910################0000000000000000PFPallet
7.62E+0910################0000000000000000SYSq yard
7.73E+0920################0000000000000000LMLinear m
7.78E+0910################0000000000000000KTKit
7.78E+0910################0000000000000000BGBag

BillingDocBillingItemPostingDatePricingDateInvGrossAmtDocCurrInvNetValDocCurrInvQtyTBInvQtySMInvQtySInvQtyRLInvQtyPFInvQtyLTRInvQtyKGInvQtyKDInvQtyGAInvQtyEAInvQtyEInvQtyCTInvQtyBXInvQtyBSalesUnitSalesUnit_txt
7.71E+09101/16/20239/16/20221715.21715.2022.5141000.062024.9300054.91200.06224.93SHSheet
7.61E+09101/16/202312/9/20226141.616455.23013.752500023.52500252502525EAEach
7.73E+09101/16/202312/26/202239933.8739933.8705305.5505000366.080005305.5005305.5RLRoll
7.72E+09101/16/20231/13/20230000000000000000KGKilogram
7.61E+09101/15/20231/15/202322972022972000280900011162.9702809024588.030011162.97GAGallon, US
7.61E+09101/15/20231/15/20230000000000000000BXBox
7.62E+09401/15/20231/15/20230000000000000000/LBper pound
7.05E+09101/16/20231/16/20230000000000000000YDYard
7.05E+09101/16/20231/16/20238408400010000.01809.9780022222UNUnit
7.6E+09101/16/20231/16/20230000000000000000SMSq Meter
7.61E+09101/16/20231/16/20230000000000000000LTLiter
7.61E+09101/16/20231/16/20230000000000000000GRGram
7.62E+09101/16/20231/16/20230000000000000000DRDrum
7.62E+09101/16/20231/16/20230000000000000000LBPound
7.62E+09901/16/20231/16/20230000000000000000CTCarton
7.62E+09101/16/20231/16/20230000000000000000FTFoot
7.62E+09101/16/20231/16/20230000000000000000PFPallet
7.62E+09101/16/20231/16/20230000000000000000SYSq yard
7.73E+09201/16/20231/16/20230000000000000000LMLinear m
7.78E+09101/16/20231/16/20230000000000000000KTKit
7.78E+09101/16/20231/16/20230000000000000000BGBag
PijushRoy
Community Champion
Community Champion

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.