Forum Discussion
Power ParticipantUnable to create Date Hierarchy
Hi,
I have a large data in my data moidel that has dozens of date fields.
Some of them have Date Hierarchies and others do not, I wlould like the applicationvalidationdate to have one like the Authority Rec Date has below. How do I do this? The applicationvalidationdate was formatted as Date/Time in Power Query, I've now updated that to Date like all the others.
Thanks
You could try turning auto date time off then back on. That might triggger the creation of the date heriarcy on the field. Please save a backup of your file before making any changes.
File > Options and Settings > Options
Current File > Data Load > Time intelligence
Un-check Auto date/time and click ok then go back in and turn it back on.
HI ArchStanton
do you have a calendar table in your model, which has a relationship to the "applicationvalidationdate" field?
When there's a relationship to a calendar table, the date hierarchy of the field in the fact table gets removed automatically. You can use the date hierarchy of the date field in your calendar table instead.Cheers
Tim
4 Replies
- jdbuchanan71
Super User
You could try turning auto date time off then back on. That might triggger the creation of the date heriarcy on the field. Please save a backup of your file before making any changes.
File > Options and Settings > Options
Current File > Data Load > Time intelligence
Un-check Auto date/time and click ok then go back in and turn it back on.
ArchStantonThank you, I toggled the auto Time Intelligence setting then I removed the inactive relationship and it worked. Not sure which reply fixed it - probably a joint effort! 👍
- timalbers
Skilled Sharer
HI ArchStanton
do you have a calendar table in your model, which has a relationship to the "applicationvalidationdate" field?
When there's a relationship to a calendar table, the date hierarchy of the field in the fact table gets removed automatically. You can use the date hierarchy of the date field in your calendar table instead.Cheers
Tim
- ArchStanton
Thank you, I added an inactive relationship earlier to see what would happen because there was no hierarchy available before - I first toggled the auto Time Intelligence setting then I removed the inactive relationship and it worked. Not sure which reply fixed it - probably a joint effort! 😊