Decimal Values

Hello,

I stumbled upon the same issue when working with Power BI and Redshift. diego's solution works as described but only if you work with positive values; a better unexpected solution is to use the Power Query editor in Power BI and apply a Standard Transformation to the column such as Multiplying the value by one or adding 0 to the value.

The standard query without the transformation casts the return value as decimal which in Redshift defaults to using 0 decimal values:

select sum(CAST("mov_div_amount" AS DECIMAL ) ) as "C1" from ( select "mov_div_amount" from "main"."public"."facmovementdivision" where "mov_div_date" >= TIMESTAMP '2018-12-31 00:00:01') as "ITBL"

After the suggested transformation is applied, the query instead returns a double and looks like the following:

select sum("C1") as "C1" from ( select CAST("mov_div_amount" AS DOUBLE PRECISION ) * CAST(1 as float8) as "C1" from "main"."public"."facmovementdivision" where "mov_div_date" >= TIMESTAMP '2018-12-31 00:00:01') as "ITBL"

This solution is viable when working with two decimals or less because the default precision for double in Redshift is 2. The ideal solution would be for the Power BI Redshift connector to avoid this cast when using numeric/decimal fields.

Here's an image of two visuals comparing the same value before and after the suggested transformation:

I hope this helps!