Forum Discussion
Post PartisanDate 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
Community ChampionRichOB Make sure it is a Date field and make sure this is turned on under File | Options and settings | Options:
RichOBHi 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
- Greg_Deckler
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.
- RichOB
I just double checked and they're both Date in power query. I've also tried adding a Year and Month column for each sheet and trying to connect them that way but it still won't change the Balance and Budget figures at the same time.