Forum Discussion

aiplbonn's avatar
aiplbonn
Regular Visitor
3 years ago

The calculation always returns a value without commas

Hey, I'm stuck on a fairly simple point. I have an import value in a field. This is usually in euro cents, but can also be in a completely different currency. To convert this foreign currency into...
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

looks like the conversion rate is also a text field, not sure if an implicit cast is messing things up.

aiplbonn's avatar
aiplbonn
Regular Visitor
3 years ago

I found the error, the exchange rate already did not indicate that it was a decimal number.

But now I have another problem. When I search for the exchange rate, I do it like this

 

LOOKUPVALUE(exchange rate[currencytoEuro],exchange rate[currency_id],VoucherData[currency_id])

 

If no matching exchange rate can be found, a 1 should be inserted.
Previously I did it like this

 

LOOKUPVALUE(exchange rate[currencyToEuro],exchange rate[currency_id],VoucherData[currency_id], "1")

 

But now PowerBi is complaining because the 1 is written as text and not as a number.

 

Expressions that result in the variant data type cannot be used to define calculated columns.

 

How do I ensure that a 1 is inserted when the exchange rate is not found?

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    just remove the " around the 1.