Forum Discussion
How to convert string (yyyy-mm-dd) to date when using directQuery?
I've created a new column with that expression:
Date Format = DATE(LEFT(PSTN[Date],4),MID(PSTN[Date],5,2),RIGHT(PSTN[Date],2))If I try to use that new column in the report, I have the following error:
Query(1,1), the function "DATE" is not allowed as a part of calculated expression of the column DAX in the models DirectQuery.
I see multiple threads with that problem but didn't find a working solution.
11 Replies
pls try this
Measure = DATEVALUE(SUBSTITUTE(MAX('Table'[Date]),"-","/"))I cannot use a measure as filter.
Hi raks13
I think, you can directly change the String to Date format for Direct Query dataset, inplace of create new column
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.
Thanks
Pijush
LinkedinWhen I do that, I cannot see the data in the table grid.
Hi raks13
In Direct Query, you cant see the data in table Grid/table view
In Report View, click on the Date column and change the data type and format
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.
Thanks
Pijush
Linkedin
Hi raks13
Here is my testing.
I used Direct Query to connect to SQL Server and this is my raw data.
Then I created a calculated column as follows.
DateType = DATEVALUE('Table_1'[Date])Calculated column can be used as filter.
Result:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, I try this also but in my case, it doesn't work. Maybe I have data that I cannot transform. It's a big quantity of data and sometimes, I have errors and when I open the next time, no error.
