Direct Query | Datetime columns don't have hierarchy

[Anonymous]

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 !   

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Regarding the cause of this problem, you can refer to this:

Modeling limitations 

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 Chen

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

[amitchandak]

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-Intell...

[rajendraongole1]

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.