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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Active-Inactive Relationship

Rishabh_Jain_31_0-1615203877913.png

Please look at my data model's relationships
Here I have a region column in WorkorderTable, HeadcountTable and EmployeeTable
I want to create a master Table like I did named EmployeeHeadcountRegionTable.
I want to select any region from EmployeeHeadcountRegionTable and that will filter data to all 3 tables.
I am not sure how to achieve that, Can you please suggest me a way.
Because if I try to create Active relationship then It shows me that you can not make one because of circular dependencies.

Please suggest me a way to do it.

3 REPLIES 3
Anonymous
Not applicable

This is not a data modeling issue in terms of relationships and schema. What you have done is correct logically. The only place you could have gone wrong is the following scenario and that sometimes happens mainly because of a lack of understanding about what happens when you create a CALCULATED TABLE.

 

For example, In case you have created a master table for Region based on any of the three fields using expressions like ALL(WorkorderTable[Region]) or ALL(HeadcountTable[Region]) or ALL(EmployeeTable[Region]), the master table returned by the ALL() (or any other table function) will inherit all the relationship of the table mentioned inside ALL() or whichever table function you have used. 

 

Therefore when you try to use it as a master table and try to create a relationship back from the calculated master table to the table that you have used inside ALL() to create the master table, it will create a circular dependency error.


To avoid this, your master table for "REGION" should not depend on these three tables. Preferably, you should create the master table in the source itself and import it into the model or upload it as an independent table that does not have any dependency on these fact tables.

 

If creating the "Region" table in the source is not viable, then a quick method is just to copy the master table contents and create another table using "Enter Data" option and paste the contents there. Then use this table as the master table and delete the one you have created using the table function. That will fix the dependency issue. But, if any new region that appears in the fact table will not be there inside this table and every time you will have to edit this table created using "Enter Data" to update the new region entries. Therefore it is not recomended. But for troubleshooting, you could try this and see if it fixes the problem.

 

Anonymous
Not applicable

Hello @Anonymous 

I haven't used any measure or calculated functions to create master region table.

 

I entered my Region's data manually and then created relationships.

 

The assumptions which you take maybe valid in that case but mine is different. I manually created the table.

 

 

Anonymous
Not applicable

Can you post a sample pbix?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.