Developing a set of reports using Direct Query (DQ) connecting to a set of Redshift tables. Going reasonably well, now encountered something I can't resolve.

In Power BI Desktop, when I do calculations on a number with decimals (i.e. not a whole number), the calculations work fine but when I publish the report/dataset to a Power BI Workspace, it truncates the decimals leading to incorrect results that don't match the exact same report when viewed in Power BI Desktop.

I looked at the SQL that was executed by each and see what I think explains the problem, but not why it works differently in Desktop vs Workspace.

The specific value (in this case, called "osat") is being retrieved in PBI Desktop using the following:

sum(cast("osat" as decimal(38, 6))) as "C1",

As you can see, the cast is using a 38,6 size argument.

The exact same measure/visual in PBI Workspace is executing the following:

sum(CAST("osat" AS DECIMAL)) as "C1",

As you can see, this expression has no size and this causes the decimal places to be truncated and the calculations in the report to be both wrong and different from PBI Desktop.

Thanks for any help.

Forum Discussion

Data Truncating

NetSuite ODBC truncation

Decimal Precision

MAX datetime truncates to date???

Truncate Warehouse SQL table