Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GSE
Resolver I
Resolver I

[Date].[Year] does not work anymore after establishing relationship

Hi all,

 

I have a source file (Apple-Combined) with a date column (data type = date) and then created a table (called Calendar) in Power BI Desktop where I use the calendar function and a year column using [Date].[Year]. The date column is also of data type date. As you can see everything works well so far.

1.PNG

However, after I establish the following 1:1 relationship between the two date columns...

3.PNG

...the Year column throws an error:

2.PNG

FYI, I have turned on the time intelligence option:

9.PNG

 

I'm out of ideas here. Does anybody have an idea what could cause this issue?

 

Best regards

GSE

 

1 ACCEPTED SOLUTION

Hi all,

 

this file is from a Udemy course so I know that it should work and I finally managed to find a solution. All it took was to delete the relationship and then establish it again. Now the [Date].[Year] column doesn't throw an error anymore but just shows the year as intended. So even with the relationship established, it shows the hierarchy.

 

Hope this helps anyone who runs into the same issue.

 

Best regards

GSE

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@GSE 

In case you have marked your calendar as Date Table as date column and date have become Key column. Or Date is a key column. the date will not more have Hierarchy

Try

Year = Year(Date[Date])

Year = format(Date[Date],"YYYY")

@amitchandak thanks for the quick reply. I have not marked the Calendar table as a date table. How do I check whether the date column is a key column?

 

I know that the DAX function Year = Year(Date[Date]) works as a solution. However, I would like to understand why this issue occured anyway.

 

Best regards

GSE

Hi @GSE ,

 

It is intended behaviour.if there is relationship on this field, it will not show hierarchy.

If there is no date hierarchy, .[year] will be invalid.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi all,

 

this file is from a Udemy course so I know that it should work and I finally managed to find a solution. All it took was to delete the relationship and then establish it again. Now the [Date].[Year] column doesn't throw an error anymore but just shows the year as intended. So even with the relationship established, it shows the hierarchy.

 

Hope this helps anyone who runs into the same issue.

 

Best regards

GSE

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors