Forum Discussion

jatneerjat's avatar
jatneerjat
Icon for Helper V rankHelper V
8 years ago
Solved

Date hierarchy not available

Hi,

 

I have a custom date column "Date" so when i create a relationship between "Date" and another date column which i am getting from DummyDate = calendarauto() then the hierarchy for the custom column "Date" gets lost which currently i am using in a visual and i am not able to filter this visual by year so how can i get the hierarchy and why i lost the hierarchy while i create a relationship.

 

Thanks

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi jatneerjat,

     

    I have made a test with based on you description, it do have this issue.

     

    Actually, when you create the relationship (Many to one or one to Many) between the tables with Date, only the date in the one side will show date hierarchy. 

     

    If you want create the relationship (One to one ) and you want to the date in your fact table show date hierarchy, you could create the relationship like below.

     

    Best Regards,

    Cherry

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    This is marked as solved but since none of the answers solved my issue I am adding my solution to help someone in the future. For me the problem was the Time intelligence check box that was not selected.

     

    Go to: File -> Options and settings -> Options -> Data Load -> and select the Time intelligence Auto Date/Time check box.

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi jatneerjat,

     

    I have made a test with based on you description, it do have this issue.

     

    Actually, when you create the relationship (Many to one or one to Many) between the tables with Date, only the date in the one side will show date hierarchy. 

     

    If you want create the relationship (One to one ) and you want to the date in your fact table show date hierarchy, you could create the relationship like below.

     

    Best Regards,

    Cherry

    • jatneerjat's avatar
      jatneerjat
      Icon for Helper V rankHelper V
      8 years ago

      hi v-piga-msft,

       

      What to do if i have one to many relationship and i want date hierarchy in Fact table?

       

      Thanks,

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    There was a thread on this earlier, I'll have to try to recreate this. In the meantime, you could create calculated columns with formulas like:

     

    Year = YEAR([Date])
Month = MONTH([Date])
MonthName = FORMAT([Date],"mmmm")
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      8 years ago

      OK, I was able to recreate this. Seems like it depends on how the relationship is configured. See attached PBIX.

       

       

       

       

    • sdhn's avatar
      sdhn
      Icon for Responsive Resident rankResponsive Resident
      5 years ago

      Can you give me example of calculated column?   I have a table with column called start date without hireacrcy .  Thanks 

    • rkennex13's avatar
      rkennex13
      Frequent Visitor
      3 years ago

      Solution provided is good, thanks for your input