Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a nested IF statement in Excel that works perfectly but I want to recreate it in the Power BI query editor:
=IF(C:C="united kingdom", E2*1.1,IF(C:C="france",E2*1.2,IF(C:C="usa",E2*1.3,"test")))
Please can someone advise the correct syntax.
Solved! Go to Solution.
Thanks for your help so far. Unfortutely I can't get my code to work.
I have tried to create a new column in Power BI that is going to contain my different variants
Syntax below:
Test = SWITCH(TRUE(),BaseData[CountryName] = "United Kingdom", BaseData[SalePrice] *1.1, BaseData[CountryName] = "USA", BaseData[SalePrice] *1.2,"")
I am getting an error saying - Expressions that yield variant data-type cannot be used to define calculated columns.
Can you help?
You can use switch / true() combo:
SWITCH( TRUE() Table[Column] = "united kingdom", [Whatever E2 is ] *1.1, Table[Column] = "france", [Whatever E2 is ] *1.2, Table[Column] = "usa", [Whatever E2 is ] *1.3, "test" )
Thanks for your help so far. Unfortutely I can't get my code to work.
I have tried to create a new column in Power BI that is going to contain my different variants
Syntax below:
Test = SWITCH(TRUE(),BaseData[CountryName] = "United Kingdom", BaseData[SalePrice] *1.1, BaseData[CountryName] = "USA", BaseData[SalePrice] *1.2,"")
I am getting an error saying - Expressions that yield variant data-type cannot be used to define calculated columns.
Can you help?
@S-J reason it is giving error coz your final condition return text and other return number. you need to change the text to number, may be zero.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
This worked for me. Thanks. S-J
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |