I am having an issue establishing a relationship between two tables, and including a cross page/report filter. For some reason, even though both attributes are based on Month level (e.g January 2016, February 2016, etc.) I cannot create the relationship. I get the following error.
"You can't create a relationship between these two columns because one of them must have unique values."
Both are named Fiscal Month, both have months in Date Format,showing January, February...
You can create a helper column that would generate the Primary key for relationships to establish.
It is neccessary to have Primary and Foreign Keys in Tables to create relationships.
You can read more on this at below Blog.
Must Read: It is by Jeffery Wang ( The Microsoft PowerBI Genius)
Hi @BhaveshPatel, thanks for responding. I believe I might have found a solution, however, do you happen to know whether it is possible to re-name a column name in the data source and after that automatically to use the new name in Power BI? If simply rename my data source column from Excel from Month to Fiscal Month, I get a message in Power BI that the column Month could not be found.
I would simply like to substitute it without affecting my graphs.
Seems strange. It does pick up the new name after the refresh as always happened to me.
@BhaveshPatelNot always, apparently, I also assume it might arrive from the Date format, tried manually in separate excel files, with the Date format (mm,dd,yyyy), and even though both columns have same naming, same date format, PowerBI cannot create the relationship. Any special setting to that?
Firstly, could you please share sample data of your tables and post the screenshot about that how you create relationship between the two tables in Power BI Desktop?
Secondly, you can directly change column name in Query Editor of Power BI Desktop, just right-click the column you want to change and select “Rename” as shown in the following screenshot. Changed column name will be automatically reelected in your graphs.
Thirdly, the above error message is caused by that you have primary key on your column and the value you are trying to create the relationship on is not completely unique. Please check duplicate values in your column.
thanks for your response. I have managed to solve the issue by creating a third table with unique values and connecting these values with the other two tables in relations One to Many. However, since both of my initial two tables have duplicates, I find it challenging to establish a direct relationship between them, something which I can solve with a third table. Regarding renaming, the issue is not in the Renaming of the column in PowerBI, but once I rename the column in my source data (e.g Excel), then PowerBi does not capture the renamed column and state it as an Error. So I have to come back to Excel and rename the column in the same way as in PowerBI.
Thanks and Regards,