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
bianchinod
New Member

Date Hierarchy not showing


I have a problem with Date Hierarchy.

After the last sw update (Version: 2.40.4554.421 64-bit (ottobre 2016)) it seams that power bi does not show the date Hierarchy.


I'm using a direct query connection and the fild in the DB is in datetime format

 

 

16 REPLIES 16
Anonymous
Not applicable

This is a frustrating issue. An easy (but frustratingly should-be-unnecessary) workaround is to create a calculated field:

 

DateIShouldntHaveToCreateThisExtraColumn = 'QueryPowerBIIsntReadingRight'[DateFromQueryThatForNoReasonIsntWorking]

The new column should give you a heirarchy without a problem.

 

And, if you're like me, it'll make you mad every time you see the duplication.

Shelley
Continued Contributor
Continued Contributor

Same problem. Created unnecessary field with date format and the hierarchy now shows. Very annoying. Thanks!

Daveed1973
Advocate II
Advocate II

As far as I am aware the date heirarchy doesn't work with Direct Query. It doesn't on any of my direct query dashboards anyway.

 

An alternative would be to create a group based on the date field and add the date in Month bins for example. Just right click on the date field and click on new group.

no, in our reports hierarchy works fine with direct query from many months, on the server test and prod

with the workaround you suggest you loose the drill capability...

Someone else has experience ? Really date hierarchy has problems with Direct Query  ?? (I have some doubt about...)


I have clarified everything, in 'direct query' mode, date hierarchies are not active, and in our reports there was used only 'import' mode and not with direct queries, thanks to Daveed1973

motoroto
Frequent Visitor

Hello, I had the same problem with an access database as source, I had a data set (with sales per date, lets call it "Sales") with a relationship to another table which contents some info I needed (such as a given promotion for a certain dates, lets call this table "Dates_Promotion"). Power Bi wont show hierarchy function if I use the date of the Sales table, but instead i use the Date from "Dates_Promotion" table and it worked, i hope it helps.

I have the same problem with a Sql server db, localhost db (while everything ok on the dbs server, test and prod)

Maybe is a bug due to the fact that dbs in the servers are in a different language from my local db

v-huizhn-msft
Employee
Employee

@bianchinod 


I download the October version and install it. The first screenshot is the source date, and I load it to Power BI desktop shown in second picture. It displays date hierarchy as expected. Please download the update and load dates, then check if it is successful. 

1.png   2.png  3.png


If you have any question, please feel free to ask.

Best Regards,
Angelia

thanks for your answer, if I import the data in Power BI (from both excel or a DataBase) it works correctly,

my problem is that Date Hierarchy is not working when I use a direct query connection to a DataBase.

 

 

 

@bianchinod 

As I tested, I import data using Power Query, the data column still show hierarchy as the following screenshot, could you please post your snapshot for further analysis?

Capture1.PNG

Best Regards,
Angelia

thanks.

in order to see the problem you should use a "direct query" connection to a DataBase (not "import")

 

Looking into the Forum I saw that maybe it could be a limit of the "direct query" connection.

 

regards

Anyone please? I am having the same issue.

 

I am having the same issue. I'm working with an Access database.

 

I managed to solve it by killing the relationship of my column Date with another table.

 

It is a work around as I can force the data column I want into one single table, but it should be look at for the long term.

 

It seems the problem has something to do with the E-R modelling rather than the actual Date Hierarchy...

 

 

 

 

I believe it is a limiation of direct query, it doesn't show date hierarchary. I created my own hierarchary to achive the funcationality.

 

Thanks,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.