Forum Discussion

iowakrz's avatar
iowakrz
Frequent Visitor
7 years ago
Solved

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....
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      Year = Year(Calendar[Date])

      Month Number = Month(Calendar[Date])

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

      Day = Day(Calendar[Date])

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

  • emmanuelm's avatar
    emmanuelm
    Regular Visitor
    3 years ago

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