Date Hierarchy for multiple datetime columns - inconsistent behavior

[K2_Gail]

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

[v-haibl-msft]

@K2_Gail

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

[K2_Gail]

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.

[v-haibl-msft]

@K2_Gail

What is the relationship between your data table and Calendar (Date) table, one to many or one to one?

Best Regards,

Herbert

[K2_Gail]

Well, initially, I was setting it up one-to-many. And when that was not working so well, I tried one-to-one.

Then, I removed them entirely, and things worked the way I wanted. 🙂

[v-haibl-msft]

@K2_Gail

I just tried with two simple tables, one Calendar table and one Sales table. They have one to many relationship with Date key as below.

The Date Hierarchy works well in following column chart. Is there any difference between us?

Best Regards,

Herbert

[BhaveshPatel]

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.