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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Super User
Super User

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.