Duplicate week error with custom date hierarchy table

[EltonjohnWick]

Hi, 

I had created a date hierarchy table manually, as the dax calandar function weeks don't tie in with my working weeks.

The table has the following rows: week, month, quarter and year. The calandar is based on 2023.

The issue is when I add in 2024 (copying 2023 and pasting below week 52 and changing year to 2024) and refresh from excel, I get the following error:

Column 'week' in table 'date hierarchy' contains a duplicate value '1' and this is not allowed for columns on the side of a many to one relationship or for columns that area primary key. 

I know it's not a primary key. So issue is looking at the relationship. I have the hierarchy pointing at multiple other tables if that helps 

Thanks 

[grazitti_sapna]

Hi@EltonjohnWick, 
You can try using all of the below points:-

Check for Duplicate Values:

• Verify that there are no duplicate values in the 'week' column for the combination of 'week' and 'year'. It's possible that there is a duplicate 'week 1' for the year 2024.
• Update Relationships:
  • Review the relationships between your 'date hierarchy' table and other tables. Confirm that the relationships are correctly set up and that there are no conflicts.
  • Adjust Hierarchy for 2024:
    • Since you manually added data for the year 2024 by copying and pasting, ensure that the 'week' numbers do not overlap or duplicate with the weeks from the year 2023. Each combination of 'week' and 'year' should be unique.
    • Refresh Excel Data:
      • After making adjustments, refresh the data in Excel to see if the issue persists. This will ensure that the changes you made are reflected in your data model.

Hope this will help you

Thank you

[v-jialongy-msft · Accepted solution]

HI @EltonjohnWick 

When you added the year 2024 to your table, you created a situation where the 'week' column has duplicate values (e.g., week '1' for 2023 and week '1' for 2024). If this column is used in relationships or as a primary key in your data model, these duplicates can cause problems.

Instead of using the 'week' column alone as a key, create a unique identifier for each row in your date hierarchy table. This identifier could be a combination of the year and week. For example, you can create a new column named 'YearWeek' that concatenates the year and week number (like '2023-W01', '2023-W02', ..., '2024-W01', etc.).

Best Regards,

Jayleny

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

[EltonjohnWick]

Thanks for your help