Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello
I have the following code in a nested if statment format
if GetVar("BU") = "UK" and [Cost Centre] >= 17000 and [Cost Centre] < 10826 then "EUR"
else if GetVar("BU") = "UK" and [Payment Currency Code] = null
then "GBP"
else if GetVar("BU") = "LM" and [Legal Entity] = "aaaaa" then "USD"
else if GetVar("BU") = "LM" and [Legal Entity] = "bbbbb" then "USD"
else if GetVar("BU") = "LM" and [Legal Entity] = "ccccc" then "GBP"
else if GetVar("BU") = "LM" and [Legal Entity] = "ddddd" then "GBP"
else if GetVar("BU") = "LM" and [Payment Currency Code] = null then "USD"
else [Payment Currency Code]
I am wondering if there is a faster way to write this in M code. I mean faster at execution time.
Thank you
Stuart
Solved! Go to Solution.
replace your if conditions with the below code
if GetVar("BU") = "UK" then
if [Cost Centre] >= 17000 and [Cost Centre] < 10826 then "EUR"
else if [Payment Currency Code] = null then "GBP"
else [Payment Currency Code]
else if GetVar("BU") = "LM" then
if [Legal Entity] = "aaaaa" or [Legal Entity] = "bbbbb" then "USD"
else if [Legal Entity] = "ccccc" or [Legal Entity] = "ddddd" then "GBP"
else if [Payment Currency Code] = null then "USD"
else [Payment Currency Code]
else
[Payment Currency Code]
in the if using and/or will evaluate every time but replaced them by a nested if can increase the efficiency.
another tips which is related to the data structure, use the condition wich can filter more rows in the first condition series.
replace your if conditions with the below code
if GetVar("BU") = "UK" then
if [Cost Centre] >= 17000 and [Cost Centre] < 10826 then "EUR"
else if [Payment Currency Code] = null then "GBP"
else [Payment Currency Code]
else if GetVar("BU") = "LM" then
if [Legal Entity] = "aaaaa" or [Legal Entity] = "bbbbb" then "USD"
else if [Legal Entity] = "ccccc" or [Legal Entity] = "ddddd" then "GBP"
else if [Payment Currency Code] = null then "USD"
else [Payment Currency Code]
else
[Payment Currency Code]
in the if using and/or will evaluate every time but replaced them by a nested if can increase the efficiency.
another tips which is related to the data structure, use the condition wich can filter more rows in the first condition series.
Thank you to all replies.
Regards
Stuart
calculate GetVar("BU") beforehand
Use List.Contains()
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
26 | |
20 | |
13 | |
10 | |
10 |