calculate column datediff

[Norbertus]

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

[v-yulgu-msft]

Hi @Norbertus,

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

[Norbertus · Accepted solution]

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

[Dog]

Hi @Norbertus

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