Forum Discussion
Datediff Problem With Direct Query
Anonymous ,
When using Direct Query, the performance of date calculations can also depend on the underlying database system. Ensure that the database query performance is optimized, and indexes are used effectively to avoid the odbc error. If your database is OK, so you should try creating a COLUMN in Power Query to perform that calculation instead. I think it will be more effective.
Kind Regards,
Marcel
Hi Marcelo.
Exceptionally in this project, I do not have and cannot optimize the database.
I need to solve it within Power Bi.
I've tried creating custom columns too, but it gives the same error.
Thanks
Keven
You tried to create on Power Query or using DAX? If you used DAX, try doing this using Power Query. If the error persists, than you can calculate the date differences as part of your ETL process before importing the data into Power BI. This would involve writing SQL queries or using database functions to perform the calculation...
Only in DAX. I know that it works in Power Query, however, in this project, we only have Direct Query and it is unfeasible to use Power Query, as well as, we cannot solve it in the bank.
The fact is, it's basic arithmetic, which could be easily solved in ETL, Banco or Power Query. But the case specifies the resolution via DAX, as it is in Direct Query.
And the difficulty lies exclusively in DATEDIFF transforming thousands of lines in days, communicating with the Bank in DQ.And if you use a direct dax like dae1 - date2, without datediff function.