Forced into many to many relationship

[ToddChitt]

I have a few simple tables. One is Sales Budget by Customer and by Month (first day of the month). I have a Dates dimension table that was created from a DAX Function. I also have an aggregate of the Dates table by year and month, with Working days. This last table also lists the first day of each month.

Now, when I try to set up relationships between Budget and Dates, I can ONLY have a Many-to-Many relationship, EVEN THOUGH the Date column in the Dates table has no duplicate values. Same for a relationship from Budget to Working Days, again with the First day of each month being a unique value in Working Days.

If I try to swith relationships to One-to-Many or Many-to-One I am told that is not valid.

Why? In both cases I have a table with UNIQUE values.

[Anonymous]

Can you post your pbix file?

[ToddChitt · Accepted solution]

Actually, I found the issue: I was merging my Date table with another table of dates that had duplicate dates, thus making the Date column NOT unique.

It's all about the data quality!