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

Date Hierarchy in Direct Query

Is there a solution to getting the date hierarchy in Direct Query?  I noted some postings saying it was not available but they are from 2016.  I'm hoping there has been a solution devised since then.  Thanks for any help.

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @iowakrz ,

 

Currently, the built-in date hierarchy is not yet available when using DirectQuery mode. You could click to upvote this idea or add your own comments.

 

As a workaround, you could create a custom date hierarchy manually. Please create [Year], [Quarter], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Quarter], [Month], [Day] columns to place them under [Date] column.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-yulgu-msft
Employee
Employee

Hi @iowakrz ,

 

Currently, the built-in date hierarchy is not yet available when using DirectQuery mode. You could click to upvote this idea or add your own comments.

 

As a workaround, you could create a custom date hierarchy manually. Please create [Year], [Quarter], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Quarter], [Month], [Day] columns to place them under [Date] column.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your solution, it is a good workaround waiting the hierarchical date into Direct Qurey . I voted for that !!!!

Anonymous
Not applicable

Hi Yuliana,

I created my [Year] and [Month] columns on Power Query then loaded these columns in the model.

Once I am on the model creation page, I created the Hierarchy on my [Date] column in the Fields pane but I am UNABLE to add any column under the hierarchy. 

Can anyone confirm whether this technic works? Or provide another solution? 

 

Thanks,

Melanie

Anonymous
Not applicable

Found out:

For those who may encounter the same issue, in the field pane, click on the 3 dots on the right of the [Year], [Month], [Quarter] or [Day] column you created and click: "Add to hierarchy". 

Done 

@Yuliana Gu, how do you go about creating the [Year], [Quarter], [Month] and [Day] column?  I am new on DAX, so what I did is select the table, and select New Column and when I entered [Year], there is an error message, enclose entire name in brackets.

 

thanks.

Hi,

Year = Year(Calendar[Date])

Month Number = Month(Calendar[Date])

Month Name = FORMAT(Calendar[Date],"mmmm")

Day = Day(Calendar[Date])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

I'm unable to use the following measure while my PowerBI using DirectQuery

 

Month Name = FORMAT(Calendar[Date],"mmmm")

 

Can you suggest someother method to get the month name?

 

Also, kindly suggest few methods to find the quarter of the year as well.

 

Thanks in advance.

 

Regards,

Param

@Ashish_Mathur, when I entered, the error message is cannot find table 'calendar'.  Am I suppose to enter as Year=Year (mytable[mystimestamp])?  My timestamp format is for example: 12/3/2018 12:00:00 PM.  Also, how do you create the Quarter?

Hi,

 

Yes.  Try this calculated column fomula

 

Year=Year(mytable[mystimestamp])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.