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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

circular dependency --help please

hi

 

i am just trying to relate tables using bridge table . where i am facing the circlaur dependency .

 

dim:

feedyard

sex

ration

 

data table :

feedyard sex ration index

alex bike 1200 0

alex car 6000   1

alex house 2000 2

 

created the bridge table :

check = CROSSJOIN(VALUES(data[Index]),VALUES('dimension'[dim]))
 
This is the error i am facing can anyone help here?
circualrdependency.jpg
1 ACCEPTED SOLUTION

Hi @Anonymous

1. copy "dimension" and paste, then i get "dimension2" table,

    in this table with only one column, "remove rows"->remove duplicates

2. copy "data" and paste, then i get "data2" table,

    in this table, click on the [index] column and select "Remove other columns", then Removed Duplicates

3.in "dimension2" table, add a custome column

 

1.png

Then expand this column

2.png

 

Best Regards

Maggie

View solution in original post

12 REPLIES 12
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

You use 'dimension'[dim] to create the bridge table, then create the relationship between 'dimension' and "check" table, so it occurs an error for circular dependency.

More introduction about avoiding circular dependency please read this article.

 

As a workaround, you could create this bridge table in Edit Queries.

Create a blank query, then paste the following code in Advanced editor

let
    Source1 = dimension,
    #"Removed Duplicates" = Table.Distinct(Source1),
    Source2 = data,
    #"Removed Other Columns" = Table.SelectColumns(Source2,{"index"}),
    #"Removed Duplicates2" = Table.Distinct(#"Removed Other Columns"),
    addcolumn=Table.AddColumn(#"Removed Duplicates", "index", each #"Removed Duplicates2"),
    #"Expanded index" = Table.ExpandTableColumn(addcolumn, "index", {"index"}, {"index.index"})
in
    #"Expanded index"

14.png

 

Or you can edit the table steps by steps with the UI 

1.Query1:

copy and paste the 'dim' table->Removed Duplicates

2.Query2:

copy and paste the 'data' table->click on the [index] column and select "Remove other columns"->Removed Duplicates

3.in Query1:

Add column from Query2 , then expand the column

 

Best Regards

Maggie

Anonymous
Not applicable

hi @v-juanli-msft

 

will this table gets refreshed will scheduled in prod?. I mean if the values (attributes)gets added to the dimension ( productname say) will this data be also reflects to the bridge table automatically without manual intervension?

 

one more question : if i paste the query using query editor will this query creates other two tales and then join ?. Will this step refreshes the data in bridge table  based on data change other two tables ?

Hi @Anonymous

1. when you add values in "dimension" or "data" table and refresh the dataset by clicking "Refresh All", it can automatically refresh in the "Query1".

1.png

 

2.

my first method is:

create a blank query-"Query1"

open the Advanced editor, paste my code, this will create a table which you want.

this new table "Query1" has joined "dimension" or "data" table together.

 

Yes, when "dimension" or "data" table refresh, table "Query1" will refresh automatically as shown above.

If you don't want table "Query1" to refresh upon the "dimension" or "data" table refresh,

you could uncheck "include in report refresh", then "close &&apply" , go to the "Data view", 

2.png

then when the "dimension" or "data" table refresh,  table "Query1" won't refresh but remain the previous values. (in Data view and Report view)

If you use "Query1" and "Query2" together to create the bridge table, you need to  uncheck "include in report refresh" for both  "Query1" and "Query2" .

 

Best Regards

Maggie

 

Anonymous
Not applicable

hi @v-juanli-msft : can you provide steps for add column please

Hi @Anonymous

1. copy "dimension" and paste, then i get "dimension2" table,

    in this table with only one column, "remove rows"->remove duplicates

2. copy "data" and paste, then i get "data2" table,

    in this table, click on the [index] column and select "Remove other columns", then Removed Duplicates

3.in "dimension2" table, add a custome column

 

1.png

Then expand this column

2.png

 

Best Regards

Maggie

Anonymous
Not applicable

hi @v-juanli-msft

3.in Query1:

Add column from Query2 , then expand the column .

 

can you send me screenshot how you did this  from query editor . I am not able to add column from another table .can you help here ?

Anonymous
Not applicable

hi @v-juanli-msft  if you used custom calculation tab for adding column .if yes can you share code please?

Anonymous
Not applicable

What does your overall table structure look like?  Do you have many bi-directional relationships?  Bi-directional 1 to many relationships are often the easy way to fall prey to this issue.  The other cause could be if you have created a loop within your actual structure, whereby you might have to make a relationship inactive and make use of USERELATIONSHIP in dax if there is a special case.

 

Anonymous
Not applicable

HI @Anonymous

 

i dont have any tables other than 3 .Ataching the screenshot . Do you mean that table which i am creating by the use of cross join is causing the issue ?

Untitled.jpg

 

Anonymous
Not applicable

I notice the Cardinality and Filter direction are blank.  Those options shouldn't be blank, and it shouldn't even let you select blank.  Something is very broken here.

 

Are you able to select cardinality?

Anonymous
Not applicable

hi @Anonymous  : I am able to select them but "ok" button is not active state .can you try this sample data from your end once and let me know if any issues at my end.

Anonymous
Not applicable

It just occured to me what is wrong.  Your building your bridging table in DAX, then trying to join that table into one of its parent tables.  Thats creating the circular dependancy.

 

Instead, build this table inside the Edit Query section.  Do this by right clicking on your query for the table you desire, click on "Reference", remove the columns you don't need and select "Remove Duplicates" in the next step.  You should be able to create your join after you close and apply  (you will first need to remove your Dax created table)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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