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
juan_pablo
Advocate III
Advocate III

Why USERELATIONSHIP causes circular dependency?

Hi,

Why a circular dependency is generated in such a simple model when I use the function USERELATIONSHIP? What's the logic of USERELATIONSHIP that causes it?

I have the following model:

 

Table1

Item

qtyOrdered

1

10

2

10

 

Table2

Box

Item

qtyAssigned

A

1

5

B

1

5

C

2

10

 

Table1 is "linked" with Table2 through an inactive relationship using Item column as a key.

Captura de pantalla 2020-11-18 195507.png

Then if I create the following two calculated columns in Table1:

 

'Table1'[Cantidad Asignada] = 
CALCULATE(
    SUM(Table2[qtyAssigned]),
    USERELATIONSHIP(
        Table1[Item],
        Table2[Item]
    )
)

 

 

 

'Table1'[Cantidad Pendiente] = 'Table1'[qtyOrdered]-'Table1'[Cantidad Asignada]

 

 

I get a circular dependency. If I activate the relationship by default (using the model view) it works fine, but using USERELATIONSHIP it created the circular dependency. Why is this? What's USERELATIONSHIP doing in the background?

6 REPLIES 6
v-shex-msft
Community Support
Community Support

HI @juan_pablo,

I'd like to suggest you take a look at the following blog about circular dependency if it helps:

Avoiding circular dependency errors in DAX 
Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

Thank you for the article. I read it before I wrote this post and that articles is precisely the one that tells me that USERELATIONSHIP should not generate the circular dependency. Look:

Table1[Cantidad Pendiente] depends on {

  • Table1,
  • Table1[qtyOrdered]
  • Table1[Cantidad Asignada]

}

Table1[Cantidad Asignada] depends on {

  • Table1,
  • Table2,
  • Table1[Item],
  • Table2[Row Number],
  • Table2[qtyAssigned]
  • Relationship between Table1 and Table2

}

 

So where is the circular dependency and why if I acivate the relationship and just sum

'Table1'[Cantidad Asignada] = CALCULATE(SUM(Table2[qtyAssigned]))

it works perfectly. Why USERELATIONSHIP creates a circular dependency? I just want to understand (@amitchandak thank you! but this time I don't need an alternative way to solve the problem, I just want to understand the reason).

 

Hi @juan_pablo,

I think it may be related to calculating the column processing mechanism. AFAIK, calculate fields(column/table) are hosted and processed on data model table levels. 

For the USERELATIONSHIP function, it should create and invoke a virtual relationship that not really existed in your tables to calculate.

If you only try to use this in the calculated column to do some common calculations it should work well.  When you try to use this field to calculate with static table field values, the current table may be interacted or integrated with the template virtual table contents.

Then power bi will try to mapping or analysis between these contents which may be caused the deadlock or unlimited iteration looping. (power bi will trace these type of operations, they will be ignored/canceled by the internal processing logic and send the notification message about these type of errors)

Notice: above are some thought of this scenario, I also not ensure if these surmise fully followed the AS tabular engine logic.

Perhaps matt can share some additional content for this.@MattAllington any insight about this scenario?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@juan_pablo , Userelation is better suited for meausres. Seem like-here it creating a reference. You can use related, when there is an active relation(New column on Many side) . Actually you do not need any relation to copy data from one table to another, you can handle that in formula . 

https://www.youtube.com/watch?v=czNHt7UXIe8&t=3s

refer

amitchandak
Super User
Super User

@juan_pablo , Create first column like

Cantidad Asignada = SUMX(Filter(Table2 ,Table1[Item] = Table2[Item]), Table2[qtyAssigned])

Thank you for your reply @amitchandak . I know your formula works, but I want to know the explanation, the theory of why USERELATIONSHIP generates that circular dependeny.

Thanks.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.