Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

calculate column datediff

Hai,

 

I used a calculated column to calculate a time between 2 dates

 

     time = DATEDIFF(item[syscreated];now();DAY)

 

I have a database with 100.000 rows, after a scheduled refresh i've got the message in the tablevisual whith the field "time" - "Can't show the data". After removing the "time" field the problem looks away.

 

Do i have to use a different formula of maby a measure and not a calculated column for each row?

What should be the formula for a measure?

 

With kind regard, Norbertus

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi,

     

    I removed the calculated column to the SQL statement in the SQL Server Management Studio.

    Now i don't see the problem anymore.

     

    Strange, I thougt that all the data changes can be made in the query-editor. Perhaps not

3 Replies

  • Dog's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    Hi Anonymous

     

    I would imagine that a calculate column is the best way forward assuming that you are wanting to report on the actual values rather than an aggregation. 

     

    Were you viewing this through the service? or do you have the same issue when viewing through PBI desktop?

     

    When you view the data in the model does the calculated column show the data ok. I am just trying to establish whether it's just a problem with the visual and not your formula. easiest way to test is to use SSMS and query the model to show the table data. 

    I've had a problem before where the dataset hadn't refreshed properly in the service after adding a new calculated column. 

     

    Dog

     

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    The calculated column formula works fine in my test. I didn't get the same message after I refresh the data manually.

     

    In your scenario, was only data updated after the schedule refresh, was the data type or table structure changed?

     

    Did the error occur only in table visual? How about this calculated column in data view?

     

    Regards,
    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi,

       

      I removed the calculated column to the SQL statement in the SQL Server Management Studio.

      Now i don't see the problem anymore.

       

      Strange, I thougt that all the data changes can be made in the query-editor. Perhaps not