Wednesday, October 23, 2019

Integer Number limitation in Power BI

By doing heavy calculations with big integer numbers I've hit Power BI whole number limit, which is "9,007,199,254,740,992".

If you try to get to any number higher than that Power BI will start rounding your results.

Here is what I've found about that problem online:
Source: Data types in Power BI Desktop

I've done my own research and here is what I've found.
Here are two queries I've created for the test:
SELECT TOP 62 [Power]=ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
       ,[Number]=POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))
       ,[Number+1]=POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))+1
       ,[Number-1]=POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))-1
FROM sys.messages;
GO
SELECT TOP 62 [Power]=ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))
       ,[Number]=-POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))
       ,[Number+1]=1-POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))
       ,[Number-1]=-1-POWER(CAST(2 AS BIGINT),ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))
FROM sys.messages;

GO
First query produces positive powers of "2" and second produces negatives.
The result in SSMS looks like this:

I've used both queries in Power BI Desktop and here is what I've got:

The result was expected, all numbers bigger than "9,007,199,254,740,992" and less than "-9,007,199,254,740,992" were rounded, but I've got something totally unexpected.

Look at the highlighted red boxes. The SUM of positive numbers produces negative Total and the SUM of negative numbers produces positive Total!

That might be a "feature", but it looks like a bug for me.

That behavior persists in the newest release: Version: 2.74.5619.841 64-bit (October, 2019)

No comments:

Post a Comment