Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAs 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
https://www.sqlbi.com/articles/using-calculation-groups-to-switch-between-dates/
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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 🙂
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |