Why USERELATIONSHIP causes circular dependency?

[juan_pablo]

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.

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?

[v-shex-msft]

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

[juan_pablo]

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

[v-shex-msft]

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

[amitchandak]

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

@juan_pablo , Create first column like

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

[juan_pablo]

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.