Cannot create active relationship

[h_l]

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

[zubairs]

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:

[PaulDBrown]

@zubairs 

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.

[zubairs]

I tried all three steps that you suggested but I'm still getting the same error.

[PaulDBrown]

@zubairs 

Did you create the dimension table using DAX or is it imported/created in Power Query?

[zubairs]

It is imported through power query.

[PaulDBrown]

@zubairs 
Did you filter out the duplicates and blank rows in Power Query?

[zubairs]

Yes it is working now, although there's another issue. Now it has created an inactive relationship with one of the tables, due to which I'm not being able to create visual in BI.

[PaulDBrown]

@zubairs 

Are you using the fields from the dimension table in the visual?

[zubairs]

Yes I am.

[PaulDBrown]

@zubairs 

That's strange. An inactive relationship has no effect until you "activate it", and adding fields from the same dimension table should be fine. You can try and delete the inactive relationship or split the dimension table into two dimension tables, but I don't see why you are getting this behaviour.

[zubairs]

Maybe my explanation might help you understand what's going on.

So I've one table which has the actual data and 3 tables that are budgets for 3 different departments.

In order to get the positions through one source for my visual, I created a position mapping table which is based upon the actual data.

Than I linked all the files (actual and budgets) to this table. The budget files are working fine, although the actual table is inactive. Plus when I'm pulling data from the tables with the dimension table as the base. The data from actual table is showing blank.

[h_l]

@zubairsReplying to the content about cannot create active relationship, do you think this is the similar situation what you are facing?

Cannot create active relationship - Have to create another table?

about the visual, in the visual, what dimension you are draging to the visual? if it was drag from inactive mapping table, that could cause the blank one.

[zubairs]

The dimension table is the one linked to all the tables. What I cannot understand is that why am I getting an inactive relationship in the first place? Also, how are relationships supposed to work in such a situation? As for me the common factor between each table is the employee position mapping, and without it my analysis is incomplete.

[PaulDBrown · Accepted solution]

@h_l 

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

[h_l]

@PaulDBrown 

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

[PaulDBrown]

@h_l 

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]

[h_l]

@PaulDBrownemm... changed but get same blank column

[PaulDBrown]

@h_l There's something wrong in the syntax. Let the Intellisense spell out the fields for you.

[h_l]

@PaulDBrown, I was using the auto-complete, or say Intellisense spell, still not working, seems I shall use "let...in..." DAX, will post another thread to ask how to get union unique value from tables.

Anyway, thank you for the answers and ideas to resolve this.

[luapdoniv]

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.