Circular dependency error

[pintobean87]

Hi all. Been reading and reading and I still don't understand how to solve this problem.

I have two calculated columns that are similar, but reference two different sets of date columns (to calculate date differences in days).  Here they both are...

_Force&Assign =

VAR max1 = MAX ( 'Table_query'[FORCE Creation Date], 'Table_query'[Date Assigned] )

VAR min1 = MIN ( 'Table_query'[FORCE Creation Date], 'Table_query'[Date Assigned] )

RETURN SWITCH(TRUE() ,ISBLANK(max1)||ISBLANK(min1), BLANK() ,CALCULATE ( SUM ( 'Calendar'[If work day] ), ALL ( 'Calendar' ), DATESBETWEEN ( 'Calendar'[Date], min1, max1 ) ) )

second one is:

_Assign&Award =

VAR max1 = MAX ( 'Table_query'[Date Assigned], 'Table_query'[Date Awarded] )

VAR min1 = MIN ( 'Table_query'[Date Assigned], 'Table_query'[Date Awarded] )

RETURN SWITCH(TRUE() ,ISBLANK(max1)||ISBLANK(min1), BLANK() ,CALCULATE ( SUM ( 'Calendar'[If work day] ), ALL ( 'Calendar' ), DATESBETWEEN ( 'Calendar'[Date], min1, max1 ) ) )

They both work fine. THE MAIN PROBLEM is that my "Year" filter slicer is not working. When I choose any year, all my visuals go "(blank)".

The current established relationship between 'Table_query' and 'Calendar' is the following

My "Year" filter slicer only seems to work when the cross filter direction is set to both, but if I do that while my two calculated columns above exist, I get this circular dependency error.

Any ideas on how to fix this? Because I am lost.

[MFelix]

Hi @pintobean87 ,

Since you are making the sum of the Calendar[Ifwork day] you are getting this circular reference. Believe that the best option in this case is not to have calculated columns but have measures instead, so you should delete this columns and create the relationship and then add the calculations has measures.

Be aware that making the shift to measure may require some adjustments to the calculations.