The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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.
Hi@EltonjohnWick,
You can try using all of the below points:-
Check for Duplicate Values:
Hope this will help you
Thank you
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.
Thanks for your help
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |