Typecasting and Accuracy for index columns

[FilipK]

I figured out, that an index I calculate in SQL is somehow changed in PBI and wonder why.

While in SQL I'm receiving 1115455083172412 as a result of:

SELECT CAST(
        (
            28996 + MAX(1625083514252 - 1577880000000) / 1000
        ) * (
            28996 + MAX(1625083514252 - 1577880000000) / 1000 + 1
        ) / 2 + MAX(1625083514252 - 1577880000000) / 1000 AS numeric
    ) AS configId1

PBI makes after querring from SQL  1115455083172410  of it. How can that happen?

[FilipK · Accepted solution]

I think it has todo with IEEE 754. I found this Last digits are changed to zeros when you type long numbers in cells of Excel - Office | Microsoft D...

By converting it in SQL to varchar(16) it works. Initially I wanted to create unique ids that are numeric instead of varchar to save some space and to get a faster report. I'll open another thread for this.

SELECT CAST(CAST(
        (
            28996 + MAX(1625083514252 - 1577880000000) / 1000
        ) * (
            28996 + MAX(1625083514252 - 1577880000000) / 1000 + 1
        ) / 2 + MAX(1625083514252 - 1577880000000) / 1000 AS numeric
    ) AS VARCHAR(16)) AS configId1

[amitchandak]

@FilipK , Can you make that 1000 as 1000.0 so it become decimal after that check the result once more

[FilipK]

@amitchandak , that is pretty interesing.

I changed it in the SQL view, but still get the wrong number.

But I'm receiving this line by filtering the column with "equal to 1115455083172412" now.

I wonder, why it behaves like this and if PBI links the data correctly to other anyway.

What do you think?

Another test I made when running the formula above by PBI itself it revealed that there are also two different results depending on the calculation method. When calculating it with a measure and display it via card visual it shows 1115455083172412 while in a calculated column it displays 1115455083172410. Is that baviour connected to my problem above?

[FilipK]

Hello. 

Here some more information, because it's very critical for us and there is no solution yet.

See what's happen, when calculate the column . I receive 2326634684249870 as a result

As soon as I display it in a table visual I receive: 2326634684249867.

Is it a PBI bug?