Losing Date Hierarchy with DirectQuery but with import it works

[sumitmishra05]

Hi,

In one of the exercise I was connected to the MS SQL Server DB using DirectQuery. One of the column named "TransactionDate", although set as DateTime data type in the source table and Date data type in PowerBI model, was not showing Hierarchy.

In other question it has been highlighted that this happens due to the mapping to other tables on the date column, so I checked that as well:

As soon as I changed the connection to import it started showing the Hierarchy:

I am looking for for the possible reason for this beahvior and a solution from this wonderful community.

Thanks.

Sumit

[parry2k]

@sumitmishra05 basically when you import, by default a hidden calendar table gets created in the model to give you the hierarchy, and as a best practice, you don't want this instead you want to create a separate date dimension in the model to use in any time intelligence use case. 

Keep in mind, each date table in the model will have its own hidden date dimension table and it can increase the size of your model drastically, so as a recommendation, turn off auto date/time under options and bring your own date dimension whether you are using import or direct query method. And in this date dimension, you can create your own hierarchy and use it in the visualization. I have a blog post that talks about how to create a date table, although there are tons of resources and material available online.

Good luck. Always follow best practice to create a scalable and fast performing data model, it is the key for any Power BI project, and all the visualization and everything becomes super simple once you have a rock-solid mode and date dimension is one of the key tables in the model and every model must have one.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak]

@sumitmishra05 , see the reason why date hierarchy is missing

https://community.powerbi.com/t5/Desktop/Date-Hierarchy-Doesn-t-show/td-p/525460
https://community.powerbi.com/t5/Desktop/Date-hierarchy-not-available/td-p/438804
https://community.powerbi.com/t5/Desktop/Lost-Missing-Date-Hierarchy/td-p/421045

You can create a date table in import mode and use that with direct query

https://www.youtube.com/watch?v=24arfrD3Qzk&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&index=6

https://www.youtube.com/watch?v=cQfJ0GmQ5os&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&index=7