CIRCULAR Dependency issue

Question

Hi Team,1) created&nbsp;RecentForecastRate Measure in&nbsp;_QBACompletionFactor_RecentMonth Dataset and it workedRecentForecastRate = _QBACompletionFactor[LatestForecastRate]2) created caculated column for the Rank column and workedRank&nbsp;=VAR&nbsp;FR&nbsp;=&nbsp;_QBACompletionFactor_RecentMonth[RecentForecastRate]VAR&nbsp;rnk&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;_QBABenchmark[Rank]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;_QBABenchmark,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;_QBACompletionFactor_RecentMonth[benchmarkjoiner]&nbsp;=&nbsp;_QBABenchmark[benchmarkjoiner]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;FR&nbsp;&gt;=&nbsp;_QBABenchmark[Min]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;FR&nbsp;&lt;=&nbsp;_QBABenchmark[Max]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;rnk&nbsp;3) when i try to create below caculated column,I getting circular dependency error. can you please help on fixing the belox DAX Syntax.&nbsp;NextTierUp&nbsp;=VAR&nbsp;next&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;_QBACompletionFactor_RecentMonth[Rank]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;_QBACompletionFactor_RecentMonth,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;_QBACompletionFactor_RecentMonth[RunMonth]&nbsp;=&nbsp;201807&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;_QBACompletionFactor_RecentMonth[MeasureID]&nbsp;=&nbsp;"RRI"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)VAR&nbsp;NextTier&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;next&nbsp;=&nbsp;5,&nbsp;5,&nbsp;next&nbsp;+&nbsp;1&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;NextTier

anonymous · Answer

Of course you get an error because using CALCULATE enforces a context transition and if you knew anything about context transition, you'd also know that context transition puts ALL THE COLUMNS' (also the calculated ones) values of the current row in the filter context. This is how circular dependency is created behind the scenes.

Please refrain from using CALCULATE in calculated columns, especially in fact tables or you'll be sorry sooner rather than later. Instead, use FILTER only with the correct conditions.

By the way, it's a bad idea to use calculated columns in DAX. You should do it in your ETL layer, that is, in Power Query. The main reason why it's a bad idea is that calculated columns do not get good compression (there are some technical reasons why). Please, try to calculate it in Power Query.

I'd suggest you also read about CALCULATE, context transition and calculated columns in depth.

Best
Darek

Forum Discussion

CIRCULAR Dependency issue

1 Reply

Related content

Circular dependency

Circular Dependency Issue

Circular Dependency

DAX Help - Circular Dependency

Circular Dependency Error