Reply
Highlighted
Frequent Visitor
Posts: 9
Registered: ‎06-17-2016

Date hierarchy is not coming when select direct query

[ Edited ]

I am facing very strange issue in Power BI desktop. I am establishing connection with Oracle DB.

 

When I select Import option while defining the connection, and selecting the date column in the Axis parameter it gives me Date hirarchy options like Year/Quarter/Month/Day. This works perfectly fine.

 

But problem starts when I select Direct Query while defining the connection and using the same query that I have used for the Import and then it does not show any date hierarchy for the date fields in the axis parameter. I have installed the latest version of PowerBI desktop 64 bit today only.

 

Please suggest.

Super User
Posts: 1,662
Registered: ‎12-15-2015

Re: Date hierarchy is not coming when select direct query

@vnchoudhary I can reproduce your issue and i think it is directquery limitation. I have seen other posts as well where users have said date hierarchy hasn't come up on direct query mode. I would say alternative to get this via directquery is to go to query editor and make duplicate copies of date column and extract year, month, quarter and date for each of those duplicated columns and then use under Axis area.

Moderator
Posts: 10,202
Registered: ‎03-10-2016

Re: Date hierarchy is not coming when select direct query

Hi @vnchoudhary,

I can also reproduce your issue when connecting to SSAS data source via live connection option. Agree with ankitpatira that it is DirectQuery limitation. 

Personally, I would recommend you vote this issue in this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14809452-improve-direct-query-dat... .


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 9
Registered: ‎06-17-2016

Re: Date hierarchy is not coming when select direct query

Thanks Ankit for your suggestion. I have tryed this and break the date in Day, month and year columns. When I create a bar chart with month field it does not show bar in Month order like Jan, Feb,.... it is coming in alphabetical order(starts from April, August,...)

 

any suggestions?

Frequent Visitor
Posts: 8
Registered: ‎11-02-2016

Re: Date hierarchy is not coming when select direct query

Hi, 

 

I had the same issue and this solution wasn't shown here, so, in case it's any help to others; 


Summary: create a custom hierarchy 


I assume there's a few ways to do this, but; 

 

In the Report view, in the Fields list, you can right click on the date column and select "Add new hierarchy". This creates a new hierarchy containing just your date column. You can then drag and drop other columns - month, quarter, year, into the hierarchy. 


If the other levels don't exist, create them in the Query editor by adding new columns to extract the month value / name.. 


Importantly, To ensure that the date values are sorted correctly when used in a visiual, each level you create should to have both a "label" column .e.g "Nov 2016" as a text string, and also a sorting column, e.g. "201611". The label column is the one added to the hierarchy. 

 

Back in the Report view, select the label column, and then from the top menu select Modelling | Sort By Column, and chose the sorting column you created.