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

[GSE]

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.

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

...the Year column throws an error:

FYI, I have turned on the time intelligence option:

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

Best regards

GSE

[amitchandak]

@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")

[GSE]

@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

[V-lianl-msft]

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.

[GSE · 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