Forum Discussion
Incorrect errormessage can´t create a relationship between two columns because one of the columns ..
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
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.
4 Replies
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"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. :)
