IF statement in CC, getting error "Expressions that yield variant data-type cannot be used to define

[JeanMariePBI]

I have a Table named "Rebates Paid" and the table has a Column that holds the Rebates Paid Amount but that value can be either USD or CAD depending on the row.   I have another column, called [Currency] which holds the currency type(either USD or CAD). 

I've created a Measure named[_CAD]  to store the exchange rate I want applied to the CAD rows only.

I'm trying to add a simple IF Statement to a Calc Column so that I can convert any rows that have CAD currency to USD, otherwise I want to use the value found in the Rebate Paid Amount.  If statement is as follows:

 =IF('Rebates Paid'[Currency]="CAD",'Rebates Paid'[Total Rebates Paid]/[_CAD],'Rebates Paid'[Total Rebates Paid])

However, I get this error message: "Expressions that yield variant data-type cannot be used to define"

Any idea why I am seeing this?

[AlexisOlson · Accepted solution]

Typically, you get this sort of error when your if returns text for one result and a number for the other. This situation doesn't look like it has text output though.

What are the data types for the [Total Rebates Paid] column and [_CAD]?

[VahidDM · Accepted solution]

Hi @JeanMariePBI 

Based on the error message, it is caused by the different data types in column.

Check the 'Rebates Paid'[Currency] and 'Rebates Paid'[Total Rebates Paid] data types.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

New Year Power BI eCard:

https://community.powerbi.com/t5/Data-Stories-Gallery/Happy-New-Year/td-p/2266398

[VahidDM · Accepted solution]

Hi @JeanMariePBI 

Based on the error message, it is caused by the different data types in column.

Check the 'Rebates Paid'[Currency] and 'Rebates Paid'[Total Rebates Paid] data types.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

New Year Power BI eCard:

https://community.powerbi.com/t5/Data-Stories-Gallery/Happy-New-Year/td-p/2266398

[JeanMariePBI]

Thank you!  This worked.  One of the columns had a AlphaNumeric data type.  Once I switched it over to Numeric the issue resolved.   Happy New Year!

[AlexisOlson · Accepted solution]

Typically, you get this sort of error when your if returns text for one result and a number for the other. This situation doesn't look like it has text output though.

What are the data types for the [Total Rebates Paid] column and [_CAD]?

[JeanMariePBI · Accepted solution]

Hello Alexis,  the data type for Total Rebates Paid and CAD are both numeric which is why I don't understand the error message.

[JeanMariePBI]

Actually, I just did a double check and the Rebates Paid did have a AlphaNumeric data type.  I've switched it over to numeric and it resolved the issue.   thank you so much!  Happy New Year!