Circular dependency error on concatenation

[Anonymous]

I know circular dependencies seem to be a regular issue, I have read the article at:

https://www.sqlbi.com/articles/understanding-circular-dependencies/

But it doesn't really help me.  I need an example of this happening woith a single table model.

I had thought my needs were fairly simple.  My source data (a single table) contains text fields for financial quarter (FIN_QTR_REC) and for financial year (FIN_YEAR_REC).

The first thing I needed was a field which concatenates these together (ie: Financial Quarter Received = FIN_QTR_REC & FIN_YEAR_REC),  This works fine.  The output for a reord from the current quarter reads "Q4 2019-20".

Secondly I needed a field which labels records depending on whether they were received this quarter or last quarter or the parallel qtr last year etc.  This must be dynamic so that the report automatically updates when the data is updated for the next quarter.  My solution for this was to create measures based on the run date of the data extraction (held in a field of trhe source table)

[Run date] = max(Data[RUN_DTE_TIME])

to hold the start and end date for each quarter of interest.  The way I accomplied this was with a switch statement:

Fin QTR Description = SWITCH(true(),

Data[DATE_COMPL_REC]>=[Curr QTR Start Data] && Data[DATE_COMPL_REC] <= [Curr QTR End Date]," (Current QTR in progress)",

Data[DATE_COMPL_REC]>=[Last QTR Start Date] && Data[DATE_COMPL_REC] <= [Last QTR End Date]," (Last complete QTR)",

Data[DATE_COMPL_REC]>=[Prev QTR Start Date] && Data[DATE_COMPL_REC] <= [Prev QTR End Date]," (Previous QTR)",

Data[DATE_COMPL_REC]>=[Curr Para QTR Start Date] && Data[DATE_COMPL_REC] <= [Curr Para QTR End Date]," (Parallel Curr QTR last year)")

The final requirement is a column which concatenates the results of these columns together:

Fin QTR Name = Financial Quarter Received & Fin QTR Description

So that I would get something like "Q4 2019-20 (Current QTR in progress)" as a label on a bar chart.

For some reason this final concatenation step throws a "circular dependency" error, which I cannot understand. The two fields:

"Financial Quarter Received", and

"Fin QTR Description"

are completely unrelated and I have not used any "CALCULATE()" functions anywhere.

If anyone has any pointers they would be gratefully received.

Thanks

Jacob

[technolog · Accepted solution]

Firstly, the "Financial Quarter Received" field is a straightforward concatenation of FIN_QTR_REC and FIN_YEAR_REC. No issues there.

The "Fin QTR Description" field is a bit more complex. You're using a SWITCH statement to determine the description based on the date. The SWITCH function is referencing various measures like [Curr QTR Start Data], [Curr QTR End Date], etc. I'm assuming these measures are derived from the [Run date] measure, which is based on the Data[RUN_DTE_TIME] field.

Now, when you try to concatenate "Financial Quarter Received" and "Fin QTR Description" into the "Fin QTR Name" column, Power BI is trying to evaluate the entire dependency tree. Even if you haven't used any explicit "CALCULATE()" functions, Power BI's engine is implicitly using them in the background to evaluate measures.

The circular dependency might be arising from the way the measures are being evaluated in the context of the new column. Here's a potential way to troubleshoot and resolve this:

Separate Calculations: Instead of directly referencing the measures in the SWITCH function, try creating calculated columns for each of the start and end dates you're referencing. This way, you're not mixing row-level calculations with measure evaluations. Once you have these columns, you can then reference them in your SWITCH statement.

Explicit Context: Sometimes, the context in which a measure or column is being evaluated can cause unexpected behaviors. Try wrapping your measures in a CALCULATE() function, even if you think you don't need it. This will give you more control over the context in which the measure is being evaluated.

Check Dependencies: Ensure that none of the measures or columns you're referencing in the "Fin QTR Name" column are, in turn, referencing the "Fin QTR Name" column. This can sometimes happen inadvertently, especially when you're working with a lot of measures and columns.

Simplify: As a troubleshooting step, try creating a simpler version of the "Fin QTR Name" column, maybe just concatenating one of the fields. If that works, gradually add complexity until you identify where the circular dependency is being introduced.

Lastly, remember that even if two fields seem unrelated, the way they're being evaluated in the context of a new column or measure can introduce unexpected dependencies. It's always a good idea to step back, break down the problem, and tackle it piece by piece.