Forum Discussion
Date Hierarchy for multiple datetime columns - inconsistent behavior
I have data coming from SQL. The table in SQL has multiple columns of type "datetime". I imported the data (it is not Direct Query).
I have created a Date table, and defined relationships. I believe this is fine, because all is working fine for SOME fields.
Some of my datetime fields do not seem to have a date hierarchy. Some of them, when added as an X Axis, show full date hierarchy. Others, used in the exact same way, do NOT show a hierarchy.
I am sure I am overlooking something, but I don't understand why some datetime fields (with proper relationships to a Date table) work as expected, and others do not.
I don't think it is because I have multiple datetime columns in my source data, because more than one of these columns works fine. Naturally, it always seems to be the one I'm most interested in actually using that won't show up with a hierarchy.
Does it matter which relationship is ACTIVE? I'm stumped...
Thanks,
G
6 Replies
I would suggest you to create a date hierarchy based on your Calender Table. Create Relationships between the Calender Table and your fact table.
You can also make inactive relationships active by using USERELATIONSHIP function.
I’m not sure what your table relationships like, but I think you can try to duplicate the date column which does not have date hierarchy in visual, and use this duplicated date column instead of the original date column in X Axis to see if it works.
Best Regards,
Herbert
So, I'm still not sure I understand WHY this works, but I removed ALL relationships between date/time columns in my data table and my Calendar/Date table. Then, all my date/time columns display proper Date Hierarchies.
I'm still pretty new to all this, so I'm not sure why I need to REMOVE the relationships, but that seems to have given me the results I wanted, at least.
What is the relationship between your data table and Calendar (Date) table, one to many or one to one?
Best Regards,
Herbert
