Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jatneerjat
Helper V
Helper V

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

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

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

v-piga-msft
Resident Rockstar
Resident Rockstar

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.

View solution in original post

8 REPLIES 8
shaliniT4T
New Member

I want date hierachy in my visualizations, please help.

Capture.PNG

Anonymous
Not applicable

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.

v-piga-msft
Resident Rockstar
Resident Rockstar

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.

hi @v-piga-msft,

 

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

 

Thanks,

Greg_Deckler
Super User
Super User

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")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Solution provided is good, thanks for your input

sdhn
Responsive Resident
Responsive Resident

Can you give me example of calculated column?   I have a table with column called start date without hireacrcy .  Thanks 

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

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.