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

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

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

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

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

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

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.