cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
t-atgeor Member
Member

Cross table filtering

Hi,

 

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...

 

Thank you.

 

Regards, Atanas

 

 

6 REPLIES 6
Super User
Super User

Re: Cross table filtering

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)

https://pbidax.wordpress.com/2015/11/04/handle-complex-relationships-inside-power-bi-desktop/

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
t-atgeor Member
Member

Re: Cross table filtering

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.

 

Thanks, Atanas

Super User
Super User

Re: Cross table filtering

Seems strange. It does pick up the new name after the refresh as always happened to me.

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
t-atgeor Member
Member

Re: Cross table filtering

@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?

Moderator v-yuezhe-msft
Moderator

Re: Cross table filtering

Hi @t-atgeor,

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.
1.PNG

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,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
t-atgeor Member
Member

Re: Cross table filtering

Hi Lydia,

 

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,

 

Atanas