cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

date no hierarchy

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
Highlighted
Super User IV
Super User IV

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?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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.

Highlighted

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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.

Highlighted

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

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

Highlighted

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. 

Highlighted

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)

 

Sincerely,

 

Lee, Hong Gwoo

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors