Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
h_l
Post Patron
Post Patron

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

h_l_4-1615096624908.png

 

 

When I try to change the properties, got following error:

h_l_5-1615096710281.png

 

 

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

 

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

21 REPLIES 21
zubairs
Helper I
Helper I

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:

 

Relationship 2.png

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

@zubairs 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






It is imported through power query.

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

remove rows.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

 

Blank.pngInactive relationship.png

@zubairs 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Yes I am.

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

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

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
Community Champion
Community Champion

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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

h_l_1-1615149648103.png

 

h_l_2-1615149672616.png

 

Thanks!

H

 

 

PaulDBrown
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrownemm... changed but get same blank column

h_l_0-1615150514525.png

 

PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@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
Resolver II
Resolver II

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.

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors