Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
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
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.
ALL function takes only column as an argument. It cannot take measure as input.
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? 🙂
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
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.
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
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |