Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

DamienW

Date Hierarchy changes when relationship created on Date table

Submitted by on ‎03-11-2018 11:48 PM

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

Status: Needs Info
See more ideas labeled with:
7 Comments (7 New)
Comments
v-qiuyu-msft
Community Support
Community Support
‎03-13-2018 08:17 PM

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 

Vicky_Song
Impactful Individual
Impactful Individual
‎03-13-2018 08:18 PM
Status changed to: Needs Info
 
DamienW
Frequent Visitor
‎03-19-2018 01:41 AM

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
Frequent Visitor
‎03-19-2018 01:56 AM

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
Not applicable
‎05-07-2018 01:38 PM

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
Not applicable
‎11-07-2018 03:32 AM

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
New Member
‎05-31-2022 01:56 PM

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

Idea Statuses