Incorrect errormessage can´t create a relationship between two columns because one of the columns ..

[Franky_Piferoen]

Hi,

I've found a lot of posts about 'can´t create a relationship between two columns because one of the columns must have unique value'.

But I think I got this error while it was not supposed to happen.

I have a date dimension table and a fact tabel, in both these tables the date field is marked as 'date/time' datatype.
When I tried to make a relationship between the date dimension and the date field in the fact table I got that error. I was sure my date dimension table only contained unique values.

I tried to make this clear to powerbi by adding an extra 'applied step' remove duplicates for the data dimension query.

But this error still happened.

I finaly solved this by removing the 01/01/0001 record from my date table(which did not occur twice).

Would this be a bug or is there another explanation for this ?

thx in advance for feedback

[Phil_Seamark]

Hi @Franky_Piferoen

How did you create your date dimension?  

[Franky_Piferoen]

Hi Phil,

The source is a dimension table in my sql database.

the definition:

let
    Source = Sql.Database(SqlServerInstancename, "MyDbName"),
    dbo_SiteEventDate = Source{[Schema="dbo",Item="SiteEventDate"]}[Data],
    #"Duplicated Column" = Table.DuplicateColumn(dbo_SiteEventDate, "CalendarQuarter", "CalendarQuarter - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"CalendarQuarter - Copy", "CalendarQuarterQ"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"CalendarQuarterQ", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"CalendarQuarterQ", each "Q" & Text.From(_, "en-GB"), type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Added Prefix", {"FullDate"})
in
    #"Removed Duplicates"

[Franky_Piferoen · Accepted solution]

Hi Phil,

some extra information

I use views in sql server as sources for powerbi.

When I remove the where DD.FullDate <> '0001-01-01' from that view and refresh the data in powerbi I get the error 'Column Fulldate' in table SiteEventDate' contains blank values and this is not allowed for columns on the one side of the many-to-one relationschip ...

When I add the where clause again in my view I can load the data without problems.

[Phil_Seamark]

It does seem like weird behaviour.

If you create a Date table in DAX using the CALENDARAUTO function, are you able to make a relationship?

Otherwise it might be a good idea to use the "send a frown" feature in PBI so the dev teams can pick up on a potential bug you have found. 🙂