DATEDIFF error in DirectQuery against SQL database

[robwhitehouse]

Hi community,

I was wondering if anyone has come across an issue using the DATEDIFF function against a SQL database in DirectQuery mode when trying to create a calculated column? Or if I am doing something just plain wrong.

I've been using DATEDIFF just fine in Import mode against Excel spreadsheets and the data is in the same format. I'm now working on converting a flat Excel report into a live SQL connection, but I'm get a nice error stating:

The following exception occurred while the managed IDbCommand interface was being used: 'DATEDIFF_BIG' is not a recognized built-in function name.

I was wondering if anyone had seen this before and if it was expected behaviour in DirectQuery. Usually the desktop app is quite clever about warning about things which won't work, but this wasn't one of them. I have also tried turning off 'allow unrestricted measures' but that hasn't worked either.

Cheers,

Rob

[Eric_Zhang · Accepted solution]

@robwhitehouse

Base on my understanding, that is a potential bug or an intentional design(maybe only the compability with Azure SQL products is considered, as they are hot and cloud).

In the DirectQuery mode connecting to a SQL Server source, the calcuated column in DAX is "translated" to Transact-SQL(T-SQL, SQL Server query and programing language). Database engine runs the translated T-SQL and returns the query result ideally.

In this case, DATEDIFF in DAX is most probably translated to DATEDIFF_BIG in T-SQL, however DATEDIFF_BIG was introduced in SQL Server 2016 on-premises, Azure SQL Database and Azure SQL Data Warehouse. So I bet a lower version on-premises SQL Server in your scenario? I think that's why you see the latter part of the error "'DATEDIFF_BIG' is not a recognized built-in function name".

Regarding the forepart "The following exception ...", that's a typical .NET language(C# in PowerBI I guess) exception when a problematic interaction happens between C# and database.

I will report this internally and our engineers will evaluate it.

[garrek99]

Hi Eric

Any updates on this issue?

TY

[robwhitehouse]

Hey Eric,

Thanks for the response. I had a look at the docs on MSDN and saw it was introduced in SQL 2016 so I'm pretty sure that's the problem, my data source is an older, on premises SQL server.

I'll work around it for now; it'd be great if this could work against older data sources as well.

Cheers,
Rob