Duplicating date hierarchies?

[pbix]

Hi experts,

I need to create a few duplicate date dimension tables in Power BI for the usual reasons (lets say 5 date tables). I'll also need to create 3 hierarchies (e.g "FY>FQ>Month>Date" + others) in each table for the usual reporting reasons. 

How can I duplicate date hierarchies across each date table without needing to maintain each date hierarchy independently?

I know I can create duplicate queries in PowerQuery but don't think I can create hierachies within PowerQuery iself?

Is that possible/have I missed something easy?

Thanks!

pbix

[BrookBracewell2]

I have a similar question, but don't think I agree with the single table with "Use Relationship" solution.  I want to easily apply the date dimension in multiplespaces accross a model- but want to maintain the built hierachies, ordering etc- is their a solution perhaps using a DAX copy of the table?

[MFelix]

Hi @BrookBracewell2 ,

If you have a single date dimension and several date within the same fact table you need to use the USERELATIONSHIP in order to have the different dates responding to a single date table (hierarchy), this is the case for example of an invoice table with Invoice Date and Due Date, one of the dates needs to have an inactive relationship.

If on the other hand you have 2 or more fact tables with a single date in each one you can related all of them with your calendar table and make use of the normal measures no need for the USERELATIONSHIP.

Can you please elaborate on your model?

[BrookBracewell2]

As an example (not the actual- but will support)
FactSales  has OpenDateID,OrderDateID,ShippingDateID,PaymentDateID

I am regularly reporting on each of these time frames so I have DimDateOpen,DimDateOrder,DimDateShipping etc.

For each Dimension I build a hierachy FinancialYear,FinancialQuater,FinancialMonthName (ordered by FinacialMonthNumber)

I need to build the Finance Date hiearchy on each Dimension table.

However the model includes a bunch of other fact tables, with a bunch of other indepdant dates so there are over 20 in total.

I dont want to have inactive relationships, and "UseRelationship" as I find it untidy. I also don't want to build the hirachy 20 times over

Does that help?

[MFelix]

Hi @BrookBracewell2 ,

Not really sure why you are saying that the USERELATIONSHIP is untidy, but this is the perfect example on that use, you have more than one date column and using the inactive relationships you can have multiple values compare to the same dimension table this will allow for mor flexebility and making or model smaller since you don't have to repeat 20 dimension tables.

This can be then take tyo the next level with the calculation groups that pull the USERELATIONSHIP however the solution go by using the inactive relationships

https://www.sqlbi.com/articles/using-calculation-groups-to-switch-between-dates/

[MFelix]

Hi @pbix ,

What are the usual reasons to create 5 date tables?

Regarding hierarchies, if the date tables are autonomous from each others you will get the hierarchy made automatically (if you have the option Time Intelligence Auto Date/Time active on the options of the PBI. Otherwise you need to setup the hierarchies manual on each table, or chart.

Regards,

MFelix

[pbix]

Hi @MFelix 

Thanks for your reply. 

Usual reasons - different fact tables with different date ranges - I believe that if a date used by an inactive relationship is not available via an active relationship I will not be able to filter on it (as the active relationship passes the filter?) + some issues with self service being a bit easier with specific named date dimensions. 

Some of my 'date' hierarchies are custom (for specific company reporting requirements) e.g. not specifically Y>Q>M>D. If I've understood you correctly, these hierarchies will not propogate automatically by changing the table into a Auto Date table. 

So, is there a way to either create a query with hierachies in PowerQuery and duplicate hierarcies within PowerQuery? Or import a table into PBI and duplicate the table with hierachies in PBI itself?

Thanks 🙂

[MFelix · Accepted solution]

Hi @pbix
The hierarchies are virtual and not present in any table configuration so you need to replicate them manually on each table.

Regarding your question about the INACTIVE relationships if you make calculated measures with USER RELATIONSHIP you will then activate the inactive relationships and filters will work according.

Don't believe the best way is to duplicate a calendar table, but youneed to do what is better for your model.


Regards,
MFelix

[pbix]

Thanks @MFelix that makes a lot of sense thanks. 

Thanks for your time. 🙂