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 + 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?

 

 

 

Need Help
  • FilipK's avatar
    FilipK
    4 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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

    • 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
        4 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?

  • FilipK's avatar
    FilipK
    Icon for Resolver I rankResolver I
    4 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