Forum Discussion

FilipK's avatar
FilipK
Icon for Resolver I rankResolver I
5 years ago
Solved

Typecasting and Accuracy for index columns

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...
Need Help
  • FilipK's avatar
    FilipK
    5 years ago

    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 Docs

     

    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

     

FilipK's avatar
FilipK
Icon for Resolver I rankResolver I
5 years ago

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's avatar
FilipK
Icon for Resolver I rankResolver I
5 years ago

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?