Date Hierarchy changes when relationship created on Date table

[DamienW]

I have a date table (DimDate), with a date column using CALENDAR, where DimDate table is on the 1 of the 1:M, and mutiple M relationships with other date fields in other tables. Some of the relationships are active, with many inactive.

Strangely the DimDate table DATE field, has lost it heirachy structure, per other posts, it seems the M side relationships would lose their hierachy, and I'm okay with that, however would rather keep the hierachy on the 1 side.

Hope that makes sense, and ideas how to change/fix it would be greatly appreciated. 

Cheers

[v-qiuyu-msft]

Hi @DamienW,

I'm not able to reproduce the issue on my side. Please try to update the Power BI desktop to the latest version. If issue persists, please share the pbix file with us is possible. If it contains sensitive data, please send to me via private message. 

Best Regards,
Qiuyun Yu 

[DamienW]

Okay, I think I found the issue, it seemed, when I have a 1:1 relationship between the date table and a KPI table, the date hierachy is removed from the date table.  

When I removed this relationship, the date hierachy returned to the Date column.

Not sure if this this is a "bug" per say or expected behaviour, but annoying regardless...

I need the relationship so I can graph KPI progress,

[DamienW]

Okay, strangely, (and not sure if this has to do with the March update) when I recreated the relationship, the date hierachies have stayed.  

So it seems it has fixed it self... 

Thanks all for helping. 

[Anonymous]

So the same thing happened to me.  I had a Date table that has functioned perfectly for almost 2 years.  Then after a recent update, it failed to now recognize it as a Date formatted column or data type. The Hierarchy disappeared. 

I checked and tried everything... I set it as a Custom Date Table, reformatted the column to text, then back to Date/Time. I turned Date Hierarch off then back on... nothing fixed it.

Then I went in and deleted the only two tables that had a one to one relationship with Date value, and it immediately recognized it as a Date again with hierarchy. Th

ere is definitely some kind of issue at work here.

[Anonymous]

I have this exact issue, in the October 2018 version of Power BI desktop.

I have a Date table created like this:

Date = CALENDARAUTO()

Set it to DATE with format MM/dd/yyyy

In my dataset I have a DateCreated which I have set to the exact same format.

When I use DateCreated without the relationship it acts as a Date Hierarchy.

As soon as I link the two tables the Hierarchy is lost and I cannot find a method to re-create it.

(I can party get around the problem by creating daily, monthly etc bins)

Very interested to find out why the date hierarchy (mis-)behaves this way!

[gabrielbreves]

I had the same problem and I'd changed the relationship type from "one to one" to "one to many" and it had worked out