CIRCULAR Dependency issue

[sentsara]

Hi Team,

1) created RecentForecastRate Measure in _QBACompletionFactor_RecentMonth Dataset and it worked

RecentForecastRate = _QBACompletionFactor[LatestForecastRate]

2) created caculated column for the Rank column and worked

Rank =
VAR FR = _QBACompletionFactor_RecentMonth[RecentForecastRate]
VAR rnk =
    CALCULATE (
        VALUES ( _QBABenchmark[Rank] ),
        FILTER (
            _QBABenchmark,
            _QBACompletionFactor_RecentMonth[benchmarkjoiner] = _QBABenchmark[benchmarkjoiner]
                && FR >= _QBABenchmark[Min]
                && FR <= _QBABenchmark[Max]
        )
    )
RETURN
    rnk

3) when i try to create below caculated column,I getting circular dependency error. can you please help on fixing the belox DAX Syntax. 

NextTierUp =
VAR next =
    CALCULATE (
        VALUES ( _QBACompletionFactor_RecentMonth[Rank] ),
        FILTER (
            _QBACompletionFactor_RecentMonth,
            _QBACompletionFactor_RecentMonth[RunMonth] = 201807
                && _QBACompletionFactor_RecentMonth[MeasureID] = "RRI"
        )
    )
VAR NextTier =
    IF ( next = 5, 5, next + 1 )
RETURN
    NextTier

[Anonymous]

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