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
vnchoudhary
Frequent Visitor

Date hierarchy is not coming when select direct query

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.

4 REPLIES 4
reubster
Frequent Visitor

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. 

 

v-yuezhe-msft
Employee
Employee

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.
ankitpatira
Community Champion
Community Champion

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

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?

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.