Forum Discussion
Date Hierarchy help please
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
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=76s
If previous post helped, then please consider Accepting it as the solution to help the other members find it more quickly
What is the data source and import mode?
+
I saw your response to Greg why do you need a hierarchy for relationships, and not connect the tables by date?It's an Odata feed from Microsoft Dynamics.
I've connected the tables by date, made a table visual with the balance and budget amounts, along with a date slicer. But when I click the Year, for example, only the balance amount will change not the both figures. Afeyr a lot of back and forth, I noticed that the only difference with the 2 sets of data is that the dates are formatted differently (unless there's something else I'm missing)
The only other 2 connectors between the 2 tables are the GL Account No which signifies what category it belongs to and another code to signify an area.
I've attached the photo of the model view. The connectors from the Category is the G/L Account No as that table has the correct names associated with the GL Account No's whereas the other tables don't. Does anything jump out at you here?
