CODEWITHSUNDEEP
daxpowerquerycircular-dependency
Sebastien Chemouny
Sebastien Chemouny

Reputation: 197

DAX : Using USERELATIONSHIP and getting a circular dependency detected error

i have 2 tables which have 2 relationships based on 2 dates : The "daily_valus" table have several products per date which (date) is connected to the weeklies table on the 2 dates (date fin adjusted & date début adjusted). The "date début adjusted" is the active one.

enter image description here

I have the following formula in a new column (ctRow) of the weeklies table :

= calculate( COUNTROWS(daily_valus); USERELATIONSHIP(daily_valus[Détail.Date];weeklies[date fin adjusted]))

The formula works and is getting the right number of rows.

However, I have another add. column and i'm just refererencing the ctRow column :

= weeklies[ctRow]

then i got that error below.

enter image description here

Does anyone knows why ?

Upvotes: 2

Views: 1116

Answers (2)

Timo Riikonen
Timo Riikonen

Reputation: 477

I managed to solve similar error by removing USERELATIONSHIP line, which was not needed in my case. So one solution should be to make the connection active and then remove this line.

Upvotes: 0

sergiom
sergiom

Reputation: 4897

It should be enough to remove the dependency from all of the columns but for the date to be used in the relationship using ALLEXCEPT

=
CALCULATE(
    COUNTROWS( daily_valus );
    USERELATIONSHIP ( daily_valus[Détail.Date]; weeklies[date fin adjusted] );
    ALLEXCEPT( weeklies; weeklies[date fin adjusted] )
)

Edit:

The circular dependency happen because when we use CALCULATE, a context transition happens, to transform the current row context on the current rows for which we are evaluating the calculated column.

This means that a filter context is set on every column of the current row, including the second calculated column, that copies the ctRow, that I'll call ctRow2. But ctRows2 depends on ctRow, therefore we get the circular dependency.

To solve the circular dependency we must remove the dependency from the result of the context transition, but keeping the filter over the column that is needed for the relationship to work, that in our case it means to use ALLEXCEPT( weeklies; weeklies[date fin adjusted] ).

Upvotes: 3

Related Questions