cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jatneerjat Member
Member

Date hierarchy not available

Hi,

 

I have a custom date column "Date" so when i create a relationship between "Date" and another date column which i am getting from DummyDate = calendarauto() then the hierarchy for the custom column "Date" gets lost which currently i am using in a visual and i am not able to filter this visual by year so how can i get the hierarchy and why i lost the hierarchy while i create a relationship.

 

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Date hierarchy not available

OK, I was able to recreate this. Seems like it depends on how the relationship is configured. See attached PBIX.

 

 

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Date hierarchy not available

Hi @jatneerjat,

 

I have made a test with based on you description, it do have this issue.

 

Actually, when you create the relationship (Many to one or one to Many) between the tables with Date, only the date in the one side will show date hierarchy. 

 

If you want create the relationship (One to one ) and you want to the date in your fact table show date hierarchy, you could create the relationship like below.

 

Untitled.png

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Date hierarchy not available

There was a thread on this earlier, I'll have to try to recreate this. In the meantime, you could create calculated columns with formulas like:

 

Year = YEAR([Date])
Month = MONTH([Date])
MonthName = FORMAT([Date],"mmmm")

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Date hierarchy not available

OK, I was able to recreate this. Seems like it depends on how the relationship is configured. See attached PBIX.

 

 

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Date hierarchy not available

Hi @jatneerjat,

 

I have made a test with based on you description, it do have this issue.

 

Actually, when you create the relationship (Many to one or one to Many) between the tables with Date, only the date in the one side will show date hierarchy. 

 

If you want create the relationship (One to one ) and you want to the date in your fact table show date hierarchy, you could create the relationship like below.

 

Untitled.png

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jatneerjat Member
Member

Re: Date hierarchy not available

hi @v-piga-msft,

 

What to do if i have one to many relationship and i want date hierarchy in Fact table?

 

Thanks,

Highlighted
tschris Member
Member

Re: Date hierarchy not available

This is marked as solved but since none of the answers solved my issue I am adding my solution to help someone in the future. For me the problem was the Time intelligence check box that was not selected.

 

Go to: File -> Options and settings -> Options -> Data Load -> and select the Time intelligence Auto Date/Time check box.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 131 members 1,676 guests
Please welcome our newest community members: