Forum Discussion
IF Statement: Expressions that yield variant data-type cannot be used to define calculated columns.
Hello,
I am new to PowerBI and I more used to working with MS Excel. Can someone tell me why the following formula will not work?
Column = if (Sheet1[Profit]=0,"No Profit", (Sheet1[Profit]))
Thank you so much for your help! :)
You have two data types there, text and a number. You'll need to convert the number to text. The best way is using the FORMAT function. I'm guessing Profit is an amount of money, meaning a decimal number with two digits after the decimal, so that would be:
Column = if (Sheet1[Profit]=0,"No Profit", FORMAT(Sheet1[Profit], "Fixed"))
Or more generically...
Column = if (Sheet1[Profit]=0,"No Profit", FORMAT(Sheet1[Profit], "General Number"))
2 Replies
You have two data types there, text and a number. You'll need to convert the number to text. The best way is using the FORMAT function. I'm guessing Profit is an amount of money, meaning a decimal number with two digits after the decimal, so that would be:
Column = if (Sheet1[Profit]=0,"No Profit", FORMAT(Sheet1[Profit], "Fixed"))
Or more generically...
Column = if (Sheet1[Profit]=0,"No Profit", FORMAT(Sheet1[Profit], "General Number"))
Thank you so much for your help!
