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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joglidden2
Post Patron
Post Patron

Date Heirarchy won't create

I have a date field, and yes, it's set to date data type under column tools. I have the obscure little 'Time intelligence' box check in settings. Data source is direct query. Every time I go to generate a heirarchy, it just duplicates itself. I can't get Year, Quarter, Month, Day. 

 

What am I missing? 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @joglidden2 ,
https://community.powerbi.com/t5/Community-Blog/Why-there-is-no-date-hierarchy/ba-p/2266682

 

Now let's see what we need to be aware here regarding the date hierarchy.

  • Enable automatic date and time
  • The table storage mode is Import
  • The column data type is date or date/time
  • The column isn't the "many" side of a model relationship

When the above conditions are met, the date hierarchy will work for you.
because your data source is Direct query, so it won't wok.


Best Regards,
Community Support Team _ Yalan Wu
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

9 REPLIES 9
v-yalanwu-msft
Community Support
Community Support

Hi, @joglidden2 ,
https://community.powerbi.com/t5/Community-Blog/Why-there-is-no-date-hierarchy/ba-p/2266682

 

Now let's see what we need to be aware here regarding the date hierarchy.

  • Enable automatic date and time
  • The table storage mode is Import
  • The column data type is date or date/time
  • The column isn't the "many" side of a model relationship

When the above conditions are met, the date hierarchy will work for you.
because your data source is Direct query, so it won't wok.


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That's the issue: table storage must be import. Therefore, Time Intelligence in PBI is useless to me. I'll put in a request to the PBI team to add this capability. Thanks for catching that. 

v-yalanwu-msft
Community Support
Community Support

Hi, @joglidden2 ,

Can you share your date column data or screenshots? Perhaps the text is not formatted to date.


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

It is formatted to date both in PBI (shown), and SQL Server (not shown). Direct Query. 

joglidden2_0-1657077687555.png

 

v-yalanwu-msft
Community Support
Community Support

Hi, @joglidden2 ,

If you have not enabled this option, then you need to enable this option first.

In Power BI Desktop, you select File > Options and settings > Options, and then select either the Global or Current File page. On either page, the option exists in the Time intelligence section.

vyalanwumsft_0-1656999881603.png

Reason 2:

When a date field is related to another non-date field (such as an integer type Date Key field). Power BI still removes the hierarchy of fields on Many side of a one-to-many relationship.

https://community.powerbi.com/t5/Community-Blog/Why-there-is-no-date-hierarchy/ba-p/2266682


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Good question, and thanks for the link. Yes, the table that I'm trying to create the date heirarchy on is on the many side of a one-many relationship. However, it is not the date field that forms the relationship. See screenshot: in red I've underlined the integer fields that form the relationship, and in blue the date field that I'm attempting to create the heirarchy with. According to the article link, this should still be allowed. 

joglidden2_0-1657062635009.png

 

 

amitchandak
Super User
Super User

@joglidden2 , Please make sure it is checked at the report level. If yes, it should generate for all the dates not joined to the date table (marked as date table).

 

Make sure you are using latest version of power bi and no windows patch is pending

Yes, good question. 'Time Intelligence' was checked for both current file and global. I should have been clear that I checked both. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.