Date Hierarchy not showing

[bianchinod]

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

[Anonymous]

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]

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

[Daveed1973]

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.

[perpor]

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

[perpor]

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

[perpor]

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

[perpor]

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]

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.

[perpor]

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]

@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. 

    

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

Best Regards,
Angelia

[bianchinod]

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.

[v-huizhn-msft]

@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?



Best Regards,
Angelia

[bianchinod]

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

[akhelif]

Anyone please? I am having the same issue.

[ddiazsolis]

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...

[parry2k]

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