How to convert string (yyyy-mm-dd) to date when using directQuery?

[raks13]

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.

[Anonymous]

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 Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[raks13]

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.

[PijushRoy]

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
Linkedin

[raks13]

When I do that, I cannot see the data in the table grid.

[PijushRoy]

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

[raks13]

My date is shown in a grid as a string:

I change the type as you said and then I cannot see the grid:

[PijushRoy]

Hi @raks13 

I am not sure what is the Grid visual you using in your screenshot
Yes, the visual earlier using Text datatype, once you change datatype it is showing error
Please try to rebuild the visual.

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

[raks13]

Hi @PijushRoy ,

Have you another idea?

[raks13]

As soon as I add the date column, it's showing error.

[Ahmedx]

pls try this

Measure = 
 DATEVALUE(SUBSTITUTE(MAX('Table'[Date]),"-","/"))

[raks13]

I cannot use a measure as filter.