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?
Solved! Go to Solution.
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?
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?
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?
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
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.
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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.