OLE DB or ODBC error: Query cannot convert value " of type text to type Numeric/Date

[aslf]

Hi, I'm trying to create a DAX for a variance with arrows (UNICHAR) but I'm giving the following error: OLE DB or ODBC error: Query cannot convert value " of type text to type Numeric/Date. Is happening specially for values where is empty. But that can happen at any point as I'm working with a live connection. This is my code:

Act to Bud Var % arrow (RPOK) =

VAR _uparrow = UNICHAR(129129)

VAR _downarrow = UNICHAR(129131)

VAR target = [RPOK (budget)]

VAR actuals = [RPOK]

VAR vari = DIVIDE(actuals - target, target, 0)

VAR _varpercentage = ROUND(vari * 100, 2) & "%"

RETURN

    IF(

        vari > 0,

        _varpercentage & _uparrow,

        _varpercentage & _downarrow

    )

I'll really appreciate any guidance in fixing my code! Thank you!

[Shravan133 · Accepted solution]

Replace your blanks with 0. either do it at the source level or in your dax.

Act to Bud Var % arrow (RPOK) =
VAR _uparrow = UNICHAR(129129) -- Up arrow
VAR _downarrow = UNICHAR(129131) -- Down arrow
VAR target = COALESCE([RPOK (budget)], 0) -- Replace blank with 0
VAR actuals = COALESCE([RPOK], 0) -- Replace blank with 0
VAR vari = DIVIDE(actuals - target, target, 0) -- Avoid division errors
VAR _varpercentage = ROUND(vari * 100, 2) & "%"

RETURN
IF(
vari > 0,
_varpercentage & " " & _uparrow,
_varpercentage & " " & _downarrow
)