Dimdate table isn't working

Hey IffyN ,

Dimension tables are usually on the 1 side of relationship and fact tables are on the many side connected by the relationship. To take date table for example (at day level granularity), you will have one record for each date (thus 1 side) and in the fact table you connect it to can have 0 or 1 or many entries on the same date (thus many). Right now the connection as per the error seems to be on the MonthNumber field. If you had created a date level granularity date table, then for one month you can have 30-31 repititions of the same monthnumber(assuming 1 year), and this is not allowed in the one-side(dimension) of a relationship. 
Please try to delete the relationship with monthnumber and update it to use the date key or date column if applicable (lowest granularity) or model the date table such that the field that use in the date table for relationship building is non-repeating.

Hope it helps!

Hi IffyN 
I’m Jaywant. You’re seeing that error because the column you used as the key in your date table is not unique (the message says MonthNumber contains duplicate value 1), and semantic-model relationships require the “one” side to have unique values. Lets do some checks:

• Confirm your date table in the lakehouse: does it have a unique date column? If not, regenerate it (see pyspark snippet).

• Add the lakehouse table to the semantic model (Model → Add table → Lakehouse → pick dim_data).

• Rename columns to clean names (no HTML tags).

• Create relationship in the semantic model: join Fact[DateColumn] -> DimDate[Date]. Make sure the direction is appropriate (single-direction unless you need bi-directional).

• Mark as date table (if you’re in Power BI Desktop: right-click table → Mark as date table → select Date column). In Fabric semantic model check the date settings too.

• Refresh metadata in Power BI Desktop or Fabric model viewer (sometimes you need “Refresh” / re-import metadata).

• Test visuals again — error should vanish.
  
  

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/

IffyN , Seem like you are joining with the year column, not date. That is why there is an issue in relation. You can create a Date table using Dataflow Gen 2, SQL or PySpark or even using DAX in the Semantic model 

This for Direct query , but can be used in fabric Warehouse or you can convert this to spark SQL for Lakehouse 
https://amitchandak.medium.com/power-bi-direct-query-date-table-in-sql-server-b5f4fe0f6d3d

For DF 2

https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1

Add a Table in the Semantic Model, All new models support DAX tables 

Date =CALENDAR(date(2018,01,01), date(2021,10,31))
,"Year", YEAR([Date])
,"Month No", MONTH([Date])
, "Qtr No", QUARTER([Date])
,"Month",FORMAT([Date], "mmmm")
, "Month Year", FORMAT([Date], "mmm-yyyy")
,"Qtr", FORMAT([Date],"YYYY-\QQ")
,"Month Year Sort", Year([Date])*100 + Month([Date])
, "Year Week No", YEAR([Date])*100 + WEEKNUM([Date],1)
, "Week No",  WEEKNUM([Date],1)
, "Week Day", FORMAT([Date], "ddd")
, "Weekday no",  WEEKDAY([Date],1)
, "Start Of Month", EOMONTH([Date],-1)+1
, "End of Month", EOMONTH([Date],0)
, "Start of Year", EOMONTH([Date],-1* MONTH([Date]))+1
, "End of Year", EOMONTH([Date],12 -1* MONTH([Date]))
, "Start of Qtr", var _rem=  if(MOD(MONTH([Date]),3)=0,3,MOD(MONTH([Date]),3))
                RETURN EOMONTH([Date], -1*_rem)+1
, "End of Qtr", var _rem=  if(MOD(MONTH([Date]),3)=0,3,MOD(MONTH([Date]),3))
                RETURN EOMONTH([Date], 3- _rem)
,"Week Start Date", [Date]- WEEKDAY([Date],1)+1
,"Week End Date", [Date]+7- WEEKDAY([Date],1)
 , "Start of FY", Date(if(month([Date]) <4,Year([Date])-1, Year([Date])),4,1)
, "End of FY", Date(if(month([Date]) <4,Year([Date]), Year([Date])+1),3,31)
)