Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Duplicating date hierarchies?

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" + oth...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago
    Hi Anonymous
    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
MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi Anonymous ,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      7 years ago
      Hi Anonymous
      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks MFelix that makes a lot of sense thanks. 

         

        Thanks for your time. :)