Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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.
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
When 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
My date is shown in a grid as a string:
I change the type as you said and then I cannot see the grid:
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
As soon as I add the date column, it's showing error.
pls try this
Measure =
DATEVALUE(SUBSTITUTE(MAX('Table'[Date]),"-","/"))
I cannot use a measure as filter.
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |