Forum Discussion
Direct Query -Date hierarchy missing in PowerBI Desktop but the format is in date/time in PowerQuery
Hi,
I am using Direct Query to extract data from SQL Server to PowerBI Desktop.
In addition, I manually added SQL queries in Advance Editor from the Power Query screen in order to only extract the columns I need.
The date/time format is working properly in Power Query as it can recognize the column is in date/time format.
Once I load the model to PowerBI Desktop, it can still recognize the column in date/time format but the date hierarchy is missing.
Is there a way to solve this problem? Thank you!
Best regards,
Emily
3 Replies
- amitchandak
Super User
Anonymous , You can recreate it if missing
https://www.youtube.com/watch?v=x6vXVJZ_eTY
These are the reasons it can be 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
Check Settings
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time
amitchandak Thank you very much on your proposed solution, it works very well if I create 1 calendar table with date hierarchy and link with relationship to my original date field from the original table.
However, I have 10 different date fields in my case and I need to create 10 slicers for each of the date fields with date hierarchy.
Your solution is working if I only create 1 calendar table as explained earlier.
There is an error message saying that I have "circular dependancy" issue when I try to create relationship for the 2nd date.
If I create a new Calendar table and try to link the relationship with my original table, I am also getting the same "circular dependancy" errors.
Is there a way to solve this problem?
v-lili6-msftCommunity Support
HI Anonymous
First, for directquery model, there is No built-in date hierarchy(auto date/time).
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time
Then for your case, you said there are ten date fields in your fact table, in that way, I would suggest just creata a new custom hierarchy for each date field, and do not create ten calendar table for each date field.
Regards,
Lin
