Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date Hierarchy Needed

Hi,

 

I need to create a Calculated Column using a date column by multiple & statements to create a unique key in the Fact Table. However I do not need the time portion of the date table. The problem is when I join the column with and the time portion goes with it.

 

A solution which I think will help if the date column was a date heirarchy, but I have that column coming from SQL. Is there a way I can create the heirarchy as I cannot use this function which is obvious as there is no heirarchy for the date to come from.

 

** PQ is not an option as I cannot do transformations since its a 'Direct Query'

 

 

Below is the output I need.

 

 

Below is what I have

 

 

Not sure if I was able to explain in the best way, but your help would go a long way! 🙂

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    You may try

    New Column = DATEVALUE([DATE])
    New Column = DATE(YEAR([DATE]),MONTH([DATE]),DAY([DATE]))

     

    If your DATE column is Date/Time type, the new column will be Date/Time as well.

     

    So that you could follow Samarth_18 's suggestion to manually change the new column 's type to Date. Or go to Column Tools --> Change Data type:

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous ,

     

    Since its a direct query you will not able to create a custom column so you can change your date column data type from date/time to date from the Model window like below:-

     

    And for enabling date hierarchy you need to enable below option from option and settings:-

     

    Thanks,

    Samarth

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Anonymous change the column type in the PQ to date instead of date/time and that will do it.

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    You may try

    New Column = DATEVALUE([DATE])
    New Column = DATE(YEAR([DATE]),MONTH([DATE]),DAY([DATE]))

     

    If your DATE column is Date/Time type, the new column will be Date/Time as well.

     

    So that you could follow Samarth_18 's suggestion to manually change the new column 's type to Date. Or go to Column Tools --> Change Data type:

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.