Forum Discussion

Haze1's avatar
Haze1
New Member
8 years ago
Solved

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])) ...
  • Anonymous's avatar
    Anonymous
    8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Haze1's avatar
Haze1
New Member
8 years ago

Thank you so much for your help!