Decimals Truncated with Direct Query in Workspace

[MarkEvansHW]

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.

[MarkEvansHW · Accepted solution]

I asked the admin for my data gateway if there were any updates pending. He indicated that there was a new version. He loaded that new version this morning and that fixed the problem. Thus, it appears this is a specific problem with Direct Query with specific Data Gateway versions.

The new version of the gateway that he installed is 3000.31.3. 

[v-gizhi-msft]

Hi,

You can try to use FIXED or ROUND function on Power BI Desktop before publishing to Power BI Service.

Best Regards,

Giotto Zhi

[MarkEvansHW]

Thanks for the suggestion. That does not work. FIXED appears to only work on an existing measure (not on the base table field) so I tried wrapping the AVERAGE function inside a FIXED function and the same issue appears: while the number is diplayed with decimal places, the number is truncated (2.8 comes across as 2.0).