Forum Discussion
Helper IIDatediff calculation error with days or weeks
Hi,
I've direct queried a datatable from Microsoft Dynamics through the dataverse function and wanted to calculate the difference in days between two dates (with one date column being a calculated date column containing date values from a related table). I used the datediff function but I only get a result if I use months or years as the interval. When I use days (or even weeks) I receive the following error message: OLE DB or ODBC error: [Expression Error]. We couldn't fold the expression into the data source. Please try a simpler expression.
Any help would be greatly appreciated. I can provide more detail if necessary.
Hi pminnov ,
Not sure why it only didn't work on days and weeks, but according to the error message, it's always related to the DirectQuery mode when there're complex data model or complex DAX. According to this blog, I guess it might be because when you use days and weeks in DAX, DAX query generated by the visuals on your report has not be folded into queries against your data source.
Additionally, you can also take a look at the discussions in the thread:
OLE DB or ODBC error: [Expression.Error] We couldn... - Microsoft Power BI Community
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- amitchandak
Super User
pminnovUnfortunately your suggestion didn't work. If I try to use that formula in the form of a calculated column then SUMX isn't recognized as a function. The formula is accepted if I use it in a measure however when I try to show the results in a table visual I get the same error as before.
v-yanjiang-msftCommunity Support
Hi pminnov ,
According to the error message, by my research, I guess it's related to the DirectQuery mode, please try to use the import mode to check.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- pminnov
The expression does work however if months or years is used as the interval (just not weeks or days). The expression did work when I did a table merge then created a new column in the merged table and calculated the number of days between the two dates per row. I'm just confused as to why days or weeks was resulting in an error.
- v-yanjiang-msft
Hi pminnov ,
Not sure why it only didn't work on days and weeks, but according to the error message, it's always related to the DirectQuery mode when there're complex data model or complex DAX. According to this blog, I guess it might be because when you use days and weeks in DAX, DAX query generated by the visuals on your report has not be folded into queries against your data source.
Additionally, you can also take a look at the discussions in the thread:
OLE DB or ODBC error: [Expression.Error] We couldn... - Microsoft Power BI Community
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-yanjiang-msft
Hi pminnov ,
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ kalyj
