Forum Discussion
Cannot create active relationship
Hi there,
It's found I cannot create direct relationship on the new added table.
From table "Queries....": Country to table "General_Performance..." Country
When I try to change the properties, got following error:
If I delete the relation between table "Queries": date to table "Date", and domain to table "Domain", then I can create the active relationship between Country and Country, --- but, after this, when I try to create "Queries": date to table "Date", and domain to table "Domain", will make these 2 relationships inactive.
May I have your ideas?
Do I have to create another table called "Country"?
If so, how to maintain the value inside?
Just copy & paste values of "Country" from 2 tables into that? - Possible to use DAX to get the values from 2 tables?
PS: later, there will be other tables contain "Country" info, AND, in those tables, the value of "Country" maybe like: Japan, Canada, India, but not jpn, can, ind, etc., so, I am confused....
Thanks for your inputs in advance.
H
Anonymous
The reason you are getting this message is because both tables have a relationship with the calendar table, and the new filter would create a conflict since it would also indeirectly filter the date field.
The way to solve this is to create a new dimension table containing unique values for country from both tables, and then joining this Country Dimension Table with both your tables in 1:* relationships.
The process is simple. Create a new table in the riboon and type in:
Dimension Country = VAR Table1 = DISTINCT ( Queries_byPage_byDate_byGeo[Country] ) VAR Table2 = DISTINCT ( General_Performance_By_Geo[Country] ) RETURN DISTINCT ( UNION ( Table1, Table2 ) )
21 Replies
Anonymous
The reason you are getting this message is because both tables have a relationship with the calendar table, and the new filter would create a conflict since it would also indeirectly filter the date field.
The way to solve this is to create a new dimension table containing unique values for country from both tables, and then joining this Country Dimension Table with both your tables in 1:* relationships.
The process is simple. Create a new table in the riboon and type in:
Dimension Country = VAR Table1 = DISTINCT ( Queries_byPage_byDate_byGeo[Country] ) VAR Table2 = DISTINCT ( General_Performance_By_Geo[Country] ) RETURN DISTINCT ( UNION ( Table1, Table2 ) )Thanks for reply.
I am quite new to Power BI, seems I get nothing retured after apply the DAX:
Could you please help further?
Thanks!
H
Anonymous
Just make sure the syntax for the tables and fields is correct. When writing the DAX, select the appropriate field from each table. For example, my text needs the ' ' to establish the table (which isn't included in the text I wrote. For example:
'Queries_byPage_byDate_byGeo'[Country]
Seems to me like the Country is not UNIQUE in the table. You can create a new unique country table and then create the relations. Use the country from the newly created unique country table in your views.
Hi Guys,
I am having a similar issue as I'm trying to create a relationship by position. I created a unique table with no duplicate positions but I'm still getting an error:
You probably have blank as a value hidden away in the dimension table (try sorting the column ascending and then descending and see if the blank turns up).
Blank values in the dimension table makes the relationship many-to-many, so you need to get rid of it by filtering it out in Power Query.I tried all three steps that you suggested but I'm still getting the same error.
