Not able to understand circular dependency in my data. Need help.

[vkomarag]

Hi,

Below are the sample columns in my data. Let me explain. I am struggling to find the circular dependency here.

Year(column)		Rating(Column)
RANK(column)		Qfactor(Measure)
PREV_QFACT(Measure)
CURR_QFACT(Measure)
YTD CALCULATION(Column)

RANK column is calculated based on YEAR column.

Qfactor measure is calculated based on Rating column

PREV_Qfact and CURR_Qfact is calculated based on RANK and QFactor.

YTD calculation column is calculated based on PREV_QFact and CURR_Qfact.

I am able to create columns. But i have an independent data with (Indicator and Url columns).

I am trying to create teh relationship between YTD indicator from one table  to  Indicator in the another table.

I am getting an error "A circular dependency is detected".

Any eye openers in this scenario. If i dont join this cross join is happening .

Apprecate your help.

Thanks

KVB

[Anonymous]

Everything about ciruclar dependencies is horrible.  They are hard to understand, and the error message doesn't help.  I feel for you.

Definately start w/ this https://www.sqlbi.com/articles/understanding-circular-dependencies/

Setting a "primary key" on your table might help, freaky  use of ALL( ) and ALLEXCEPT( ) may help... but go read the blog, and maybe pray.

[vkomarag]

Yeah. I read the article. What i understood from that article is  CALCULATE command consider all the columns when trying to do calculation. In my example, PREV and CURR_Qfact are measured using CALCULATE command.

Is there anyway  to calculate Qfactor for the year 2015 without using CALCULATE command.

Currently i use 

PREV_Qfactor=CALCULATE([Qfactor],FILTER(Sheet1,Sheet1[Year]="2015"))

CURR_Qfactor=CALCULATE([Qfactor],FILTER(Sheet1,Sheet1[Year]="2016"))

Any alternative ? Please help.

Thanks

KVB

[Anonymous]

It would be awesome if we can convince @marcorusso to come help us 🙂  I was getting killed yesterday.

So, as the article says... when you use calculate( ) on a calc column... it is going to create a dependency to ALL the other columns, when converting it's row context to filter context.  First calc column will be fine... but once you add a 2nd, they depend on each other.

Setting a primary key can help, otherwise... we are trying to figure out "how do I remove the dependency on the other calc column?

My best guess would be to add   ALL(MyTable[CURR_Qfactor]) on the PREV calc and ALL(MyTable[PREV_Qfactor]) on the Curr calc... but, we are at the edges of my knowledge. 

[vkomarag]

ALL function takes only column as an argument. It cannot take measure as input.

[Anonymous]

I mean something like... instead of what you have:

PREV_Qfactor=CALCULATE([Qfactor],FILTER(Sheet1,Sheet1[Year]="2015"))

CURR_Qfactor=CALCULATE([Qfactor],FILTER(Sheet1,Sheet1[Year]="2016"))

try someting like:

PREV_Qfactor=CALCULATE([Qfactor],FILTER(Sheet1,Sheet1[Year]="2015"), ALL(Sheet1[CURR_Qfactor]))

CURR_Qfactor=CALCULATE([Qfactor],FILTER(Sheet1,Sheet1[Year]="2016"), ALL(Sheet1[PREV_Qfactor]))

As from my best reading of the article on cirucular reference suggests ... that may help? 🙂

[vkomarag]

PREV_Qfactor and CURR_Qfactor are measures. I am trying to make the formula as suggested. 

PREV_Qfactor=CALCULATE([Qfactor],FILTER(Sheet1,Sheet1[Year]="2015"), ALL(Sheet1[CURR_Qfactor]))

CURR_Qfactor is a measure.. ALL function acepts only columnname as argument. It does not accept measure.

Any alternative.Please suggest.

Thanks

KVB

[Anonymous]

@Greg_Deckler you have knowledge here?

[Greg_Deckler]

If I am reading this correctly, the circular dependency is caused by your YTD CALCULATION being a column instead of a Measure. If you change it to be a Measure, you should get rid of the circular dependency. Otherwise, you have the issue where YTD CALCULATION as a Column depends on the measures PREV_QFACT and CURR_QFACT but the CALCULATE for PREV_QFACT and CURR_QFACT brings in the YTD CALCULATION column and presto, circular dependency.

Would be great to have just a little sample data and all of your formulas to recreate. For example, I am not sure if your Rating and QFactor are in different tables or the same table.

[vkomarag]

Yeah. I tried the measure .It's working. But the problem is YTD indicator is having a relationship to an Indicator column of another source. If i make it a measure then i cant make a relation between a measure and column.

Hope you got my problem.

I am just strucked by this behaviour.

Thanks

KVB