Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.