Date hierarchy applied to non-related field

[tolga]

Hello,

I am having a strange issue with my date fields.

I have a table (Case) that holds data for support tickets. The table has three date fields, but the interest is on two: Date Created and Date Closed. I also have a Date Table.

In the relationships, Date Created is linked to the Date table as follows:

However, in the Case table, the Date Hierarchy appears under the Date Closed field, whereas I would expect it to be under Date Created field, as Date Created is the field that is linked to the Date table:

I cannot move Date Hierarchy under the Date Created field, which is the most important date field in the table.

I tried deleting and re-creating the relationship, removing the Case table and adding back, restarting PowerBI ... but nothing helped. Removing "Auto Date/Time" from the options remove the hierarchy, but if I do it, I cannot drill down in the charts that use Date Created field.

Any ideas?

Thank you.

Tolga

Edit 1: No idea on why images keep disappearing - trying to add them through "Insert Photos" in every edit, but they do not show up in the post.

Edit 2: Tried unchecking Auto Date/Time option, which presented a different challenge.

[amitchandak]

Refer, if this blog can help you : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.

[tolga]

Hi @amitchandak ,

Thanks for your reply, but unfortunately it did not help. I only have one relation from the date table to the case table. And I don't need another date relation to another date field.

I think I am missing something very basic, very fundamental.

Regards,

Tolga

[v-kelly-msft]

Hi @tolga,

Once you have created a relationship between 2 tables,the linked field will be removed data hierarchy,the only choice for you is to create the hierarchy manually.

Go to the "fields">right click on the field>new hierarchy:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[tolga]

Thanks @v-kelly-msft . This is the key explanation:

Once you have created a relationship between 2 tables,the linked field will be removed data hierarchy

Is there a reason why PowerBI is designed this way? Understanding this will help me understand better how the product works and on what areas I need to be more careful.

[v-kelly-msft · Accepted solution]

Hi @tolga ，

Yes,exactly.

See the similar threads below:

https://community.powerbi.com/t5/Desktop/Date-hierarchy-missing/td-p/678089

https://community.powerbi.com/t5/Desktop/Date-field-looses-Date-hierarchy-when-have-relationship/td-...

Here is a related reference.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[tolga]

Thanks very much @v-kelly-msft ! All clear!