Date not showing in direct query

[Anonymous]

Hello,

Whenever I connect with our database and select the option 'Direct query' to get a live connection, the date field doesn't show up as date. But if I import the query the date field does show up as a date. The solution can be very easy by importing the date, however it's really important for my company to have a live connection with the database.

The first row is the direct query version and the second one is the imported version. There is no difference in these dates since it's the exact same data.

I hope anyone has a solution for this.

[Icey · Accepted solution]

Hi @Anonymous ,

The built-in date hierarchy isn't available when using DirectQuery. You could click to upvote this idea or add your own comments.

---

• No built-in date hierarchy: When importing data, every date/datetime column will also have a built-in date hierarchy available by default. For example, if importing a table of sales orders including a column OrderDate, then upon using OrderDate in a visual, it will be possible to choose the appropriate level (year, month, day) to use. This built-in date hierarchy isn't available when using DirectQuery. If there's a Date table available in the underlying source, as is common in many data warehouses, then the DAX Time Intelligence functions can be used as normal.

---

Reference: Modeling limitations when using DirectQuery in Power BI

It is suggested to manually create a custom date hierarchy in your underlying source or just in Power BI Desktop. 

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[HotChilli]

I think it means you don't get the automatic date hierarchy features of a date column when you connect using direct query.

Usually you would have a date table in the source system with columns to handle any hierarchy, for example, month, week, year etc.