How to get back a date hierarchy once the a relationship has been established

[Anonymous]

Hello  -  Once you add a relationship to any table that has dates, you lose the date hiearchy for that table.  

Is there a way to manually, thru DAX, etc, to recreate the date hiearchy?   I guess you could do it by splitting out the date column in Power Query into year, month, etc.    

In my particular case, I need to use the Month of my Order Date in a slicer and I can't use the Date Table month, because these two tables are not related.   

[parry2k · Accepted solution]

@Anonymous bad idea to use hidden tables, just from performance, scalability point of view. The answer to your questions is:

1) it is not connected to the date table (or I would just use the date table month), -> Yes you can use month from the date table and use the measure that has USERELATIONSHIP using Order Date table. Think if you have another date column tomorrow, how you will work on it. Always want to think thru how the solution is going to scale. You have all the components in place, just a matter of using it.

and 2) there is no hierarchy available to me...hence the need to create one manually.  -> It is part of model development, it is less than 10 seconds to create an hierarchy and then can be used everywhere. I don't see why this could be a road blocker, but rather it gives you the opportunity to create the hierarchy the way you want or create more than one if you want.

✨ 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.⚡

[parry2k]

@Anonymous the inbuilt date hierarchy is based on the hidden table which is *not* a good idea to use. Once the relationship is created, the hidden table is removed and you can add your own hierarchy the way you want.

End of the day, as a best practice, you don't want hidden date tables in your model, coz each date table will have its own hidden table, and your model will be bloated for no reason and you will also not have a common dimension.

Add a date table and create a hierarchy in there and use that for visualization.

Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

✨ 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]

@parry2k    
Yes, I do have a date table, and it is marked as the date table.   I have not turned off the auto date hiearchy for exactly this reason though.   When you do that, you lose all of the date hierarchies that get built  (which I use frequently).   

In this particular case, my active relationship is between Order due date, and the date on the date table.  

The inactive one is the Order date  (which is the one I need a date hierarchy for).    Sure you can use "userelationship" to sum up quantity sold by order date or something like that.   

But in my case, I actually want to use the Order Date  (month) in a slicer at the top of my report page...but cannot because 1) it is not connected to the date table (or I would just use the date table month), and 2) there is no hiearchy available to me...hence the need to create one manually.