Forum Discussion
Direct Query | Datetime columns don't have hierarchy
Hello everyone,
I'm creating a dashboard using a direct query from a sql server view.
Problem:
I have several columns (datetime) and PowerBI recognizes them as so but it doesn't show the icon as a "date field" and I can't create a hierarchy based on month and year for example.
Thanks for your feedback !
Hi Anonymous ,
Regarding the cause of this problem, you can refer to this:
But you can create hierarchical dates manually.
First, creating [Year], [Quarter], [Month], [Day] calculated column. ( You need to use YEAR(), QUARTER(), MONTH(), DAY() function)
Second, select the three points to the right of the column.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
Regarding the cause of this problem, you can refer to this:
But you can create hierarchical dates manually.
First, creating [Year], [Quarter], [Month], [Day] calculated column. ( You need to use YEAR(), QUARTER(), MONTH(), DAY() function)
Second, select the three points to the right of the column.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try to manually with custom date like [Year], [Quarter], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Quarter], [Month], [Day] columns to place them under [Date] column.
hope it helps.
You can bring your own time dimension, that would be better. Refer: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885
