date no hierarchy

[MonkeyBoy]

Complete beginner question. Using Power BI Desktop, connected to a SQL Server database with a database datetime field called ReferenceDate. On the Modelling tab, this field shows as a "Date" Date Type and the formatting is showing the date format I require. All is good.

However when I drag that field into the Axis field, it doesn't create a hierarchy. I've read that hierarchies automatically appear for date and/or time fields but it's not working for me. Am I missing something?

[Anonymous]

I'm having the same issue!

I introduced another query with the name date as I wanted to create a relationship between two tables that were completely different from each other. By bridging them with a familiar set of data such as the date column, I could collect data from each of them.

The problem started as soon as I created this table. I went back to another dashboard and noticed that the 'Document Date' in one of my dashboards was was no longer working. It wouldn't let me use it's hierachy...

Any ideas?

[Greg_Deckler]

What type of visual are you using? I just tried this on a column chart and it worked no problem. Tried this with both a Date field and a Date/Time field. Are you on the latest version of Power BI?

[MonkeyBoy]

The current view is a stacked column chart however I have tried several different types.  

I downloaded MS Power BI about Thursday last week and I found this under Options/Diagnostics : Version: 2.32.4307.641 64-bit (February 2016)

Thanks for the quick response.

[Greg_Deckler]

@MonkeyBoy - One other question, are you connecting to database or tabular with SQL Server and if tabular, are you importing or using DirectQuery (connecting).

[MonkeyBoy]

Sorry for ignorance but I don't know what Tabular is. I have a database has a view over a star schema/dimensional model that I pointed MS Power BI at. I used the DirectQuery option to connect to that view.

[Greg_Deckler · Accepted solution]

Sorry, by Tabular I was referring to SSAS (SQL Server Analytics Services) or basically a cube. Although a cube is not actually Tabular. In any event, the question was directed toward whether or not you were using DirectQuery. I highly suspect that is the issue. Using DirectQuery puts a fair number of limitations in place. I just tried this with a simple SQL database table and it worked just fine. I suspect that if you import the data versus use DirectQuery that it would work just fine.

[MonkeyBoy]

Sensational. Double clicked on DirectQuery at bottom right, allowed me to switch. Chugged away for 20 seconds doing things and then I created a new visual. Dragged in the ReferenceDate and it worked. Shame it didn't explain the limited functionality when I was creating the direct query. This also means that you need to setup some process to refresh the data but I'll get to that eventually.

Thanks again. 

[extri13]

Dear MonkeyBoy,

I have a same problem as you had like on the post I wrote today

Could you please tell me the details how to solve the problem?

I could not find where the " Double clicked on DirectQuery at bottom right, allowed me to switch."

Problem details (Posted on Power Bi Fourm)

• https://community.powerbi.com/t5/Desktop/Date-hiearchy-does-not-work-automatically-in-case-of-Direct...

Sincerely,

Lee, Hong Gwoo

[AdamBuckley]

On the bottom right of your report page, there somehing that says 'storagemode (click to change)'

If you click it, it'll pop up with a message about switching all tables to import mode.

So ... it looks like i'll have to write SQL to get the date parts I need - because I need to use direct query also.

Cheers

[Sokon]

This issue doesn't seem to be solved. If you want to support the referring idea, you are highly welcome: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16835431-automatic-date-hierarchy...

[MonkeyBoy]

OK, I'll see if I can switch to an import and give it another crack.