Error: circular dependency

[gonrodrigues]

Hey!

Can someone explain why is this happening pls?

Thanks.

Relationship error

[MattAllington]

can you post the formula please?  Also, translate?

[gonrodrigues]

QP Row Count = IF(COUNTROWS(RELATEDTABLE(QP))>0;COUNTROWS(RELATEDTABLE(QP)); 0)

Difference From Prior Month = 'Variação'[QP Row Count] - CALCULATE(SUM('Variação'[QP Row Count]);DATEADD('Variação'[Date];-1;MONTH))

data is date

variação is variation 

🙂

@MattAllington

[MattAllington · Accepted solution]

I assume these are calc columns, is that correct?  I am aware of a circular dependency "issue" when you write multiple calc columns, and their incrementally refer to all columns in a table. It is very complex and I don't think I can explain it based on what I see. But my suggestion is that you don't write calc columns at all, but instead write measures. Read my article about the difference here. http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

[VivekPBI]

In my case If i create measure instead of colum ,it doesnt allow colum name ( (VW_ARC_DETAILED_TRACKER[FLAGS]) into measure.

YTD varriance = IF(VW_ARC_DETAILED_TRACKER[FLAGS]="POST",DIVIDE(CALCULATE([YTD ACTUAL]),CALCULATE([YTD PLAN]))-1,
1-DIVIDE(CALCULATE([YTD ACTUAL]),CALCULATE([YTD PLAN])))

Please help..Thanks in advance !!!!!!!

[Kashuo]

Hi, I have a similar problem to the original poster.

I would be happy to use a measure, as in the tabular view it gives me the result I need. 

However, you can't use a sliding date slicer with measures, only columns. Any ideas?

[MattAllington]

Why can't you put the slicer on the date column in your calendar table?

[Kashuo]

I can in the first table, and it works.

But in another table, I'm trying to agregate by earliest date from the first table. This has been possible in the past if I merge the tables and then expand the date column by min only, but the refresh takes hours on millions of rows. 

I was able to solve this before with the help in this thread, but now something has changed. Instead of building up the original zone table from some merges myself, an engineer created a view on the central database for me. I thought this would be better, but now the millions of rows problem has returned. So again I tried to use Calculate to solve it, like in that thread, but this time I'm getting the circular dependency error, which is new.

[TimKroemer]

Hi Matt

Appreciate this is an old thread however I am facing the same issue - I am trying to calculate the maximum date in another table (that has been linked by a unique ID) - specifically, I have a table of Material Requests and a table of Purchase Orders - I want to know when was the last date that a Purchase Order was raised against this particular Material Request.

The formula I am trying to use is as follows:

Column = CALCULATE(MAX(PO[PO Date]),FILTER(PO,PO[UID]=EARLIER(MR[UID]))

this is giving me a circular dependancy error. I have tried taking your advice and using a measure but (at least at the auto complete level), this does not allow me to compare the UID in the PO table to the UID in the MR table.

I hope this is clear - any guidance or advice would be hugely appreciated.

Gratefully,

Tim

[MattAllington]

well it looks to me like there is nothing wrong with your calc column. The issue is likely to be that you have other calc columns already.  If your table doesn't have a primary key, you can't create multiple calc columns.  Does this data need to be in your fact table?  could you instead create a new calculated table that has this information?  That should solve the problem

[bhrdrms]

Hi @MattAllington,

I found you today and ı read your blog. Nice to meet you 🙂  I would want to come your course but I live in Turkey.

ı have a problem that ı take"circular depency error", when ı have multiple calculated columns thay you say. 

But ı must do some calculate. can you give me advice.  

I have two tables, 1) invoices 2) exchange rate (for USD value) ( this table has "usd banknote selling value" for everyday)

I want to take baknote seeling from secondly table according to date. I can it below, it works.
CurrenciesBanknoteSelling_grandtotalprice =
IF(invoices[Currency_code_from_manuel_original]="TRY";
     invoices[new_invoice_grandtotalprice] / LOOKUPVALUE(currency_value[Column1.Currencies.BanknoteSelling];
                                                                                                    currency_value[Date_wholenumber];
                                                                                                    invoices[invoicedate_wholenumber]); 
invoices[new_invoice_grandtotalprice]) 

But  ı want to do same formula for grandtotalprofit.  And ı give "circular depency error". 😞

Can you give me advice,

Thank you,

Best regards,

[MattAllington]

It's hard for me to understand what is going on here from the description.  Any chance you can produce a sample workbook with non sensitive data and post a link here so I can take a look?  And also try to explain how you want to use what you are trying to build.  Chances are you are not doing it the best way, but unless I know what you are trying to do this for (ie how you will use it) then I can't really comment.

[gonrodrigues]

Yes they are calc columns @MattAllington

Thanks for your opinion and for the article! 🙂