Forum Discussion
Date Hierarchy help please
Hi, I'm trying to get the date hierarchy in the top sheet (the same as GL Entries 20 below) but the top one is not changing.
In Transform Data, I've made both Short Form and the same data type as Date. What else can I do to get the hierarchy, please? FYI I can't change the source data.
Thanks
Create a Date table and create relationships from the dates in fact tables to the date dimension. Then, build the hierarchy yourself in the Date dimension table in the model.
https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tablesHi all,thanks for the quick reply, I'll add more.
Hi RichOB ,
Regarding your question, please mark this relationship as active.Inactive relationships cannot pass filter conditions.
An additional question, is this relationship useful? If not, please delete it. If it is useful, mark this relationship as inactive and use 'USERELATIONSHIP' to use this relationship. It is not recommended to form a closed loop relationship between tables.
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn
Regarding the issue with date hierarchies not being created automatically, try this.
Select your date column
Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Wenbin Zhou
12 Replies
RichOB Make sure it is a Date field and make sure this is turned on under File | Options and settings | Options:
Hi Greg_Deckler ,
The auto date / time was already clicked green. Do you have any other suggestions?
I'm trying to create a relationship between the 2 dates so a slicer will work for the balance (in 1 sheet) and the budget (in the other sheet). I've tried manually creating the hierarchy, but making the connection in the model view was not possible. It was not giving me the newly created hierarchy as an option to drag to the other one.
Thanks
RichOB You *shoudn't* need them to both be a hierarchy to create the relationship. Is one maybe a DateTime column while the other is Date? You'd have to check the Power Query queries. Well, no actually both should work with auto time intelligence and create a hierarchy. Is it perhaps being brought in as text? Really trying to understand how you would get a date without the hierarchy. Going to try some things.
Hi RichOB
If you need an automatic Date hierarchy you need to on the " Auto date/time hierarchy " from the settings :Not recommended, here is an explanation of why:
https://www.youtube.com/watch?v=XSBA836S4p8
OR You can use a calendar table and create the hierarchies manually:
https://www.youtube.com/watch?v=xnWZrIV_g9s&t=76sIf previous post helped, then please consider Accepting it as the solution to help the other members find it more quickly
Create a Date table and create relationships from the dates in fact tables to the date dimension. Then, build the hierarchy yourself in the Date dimension table in the model.
https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tablesHi Anonymous thanks for your reply.
I made the date table "Calendar" and made the relationship to the GL Entries20 and GL Budget270 tables Date > Date. But when I made a slicer to change the table visual (that has the Balance and Budget figures), it only changed the GL Budget270 figures for some reason. I can't for the life of me figure out why.Hi all,thanks for the quick reply, I'll add more.
Hi RichOB ,
Regarding your question, please mark this relationship as active.Inactive relationships cannot pass filter conditions.
An additional question, is this relationship useful? If not, please delete it. If it is useful, mark this relationship as inactive and use 'USERELATIONSHIP' to use this relationship. It is not recommended to form a closed loop relationship between tables.
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn
Regarding the issue with date hierarchies not being created automatically, try this.
Select your date column
Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Wenbin Zhou
