=MEDIAN() results in circular dependency

[fuelgun]

I am attempting to create a Calculated Column that represents a Goal value based upon what type of facility a customer shopped at.

My KPI currently shows the median shopping duration of our customers.  I want to add a goal to the KPI that will update when the end user selects different store locations as we have 3 different store types that may have vastly different shopping durations.  If no location is selected in a slicer then I'd like the goal to represent the overall median, but the goal would update if a single location is selected to only show the median duration for the store type of the location selected.   

I started by writing an =IF( [Location Type] = "Grocery" , Calculate( Median( [Shopping Duration] ) , [Location Type] = "Grocery" ) , 0) 

The problem is that when I write a formula that includes the calculated column [Location Type], it returns a circular dependency error.  

I cannot figure out what could be causing the circular dependency error as the [Location Type] and [Shopping Duration] columns do not depend on each other at all!

[Location Type] = LOOKUPVALUE( 'mappingTable'[Location Type] , 'mappingTable'[Store Name] , 'dashboard'[Store Name])

There is a 1:Many relationship between the [Store Name] columns in my datasets where Cross Filter Direction = Both and I've Applied security filter in both directions.  

[Shopping Duration] = [Departure Time] - [Arrival Time]

[d_gosbell]

The problem here is not the Median function, at a guess it's most likely that you have 2 calculated columns calling the CALCULATE function. The problem with this is that in order to build the context in which to calculate a value, the CALCULATE function has to read the values off every other column in the same row.

Therefore if you have 2 calculated columns trying to call CALCULATE they both require the value off each other in order to build the evaluation context (also known as the "filter context").

Typically the resolution for these sorts of issues is to build your calculation as a measure instead of a calculated column.

[fuelgun]

Unfortunately, I'm not using =CALCULATE() in any column.

[d_gosbell]

---

@fuelgun wrote:

Unfortunately, I'm not using =CALCULATE() in any column.

---

But you are attempting to create one with the expression using median, so that is one. Do you have any calculated columns that reference measures? (Measures are always wrapped in an implied calculate() call). Those are the only sort of "tricky" circular dependencies. Having bi-drectional filters can also compound this issue as you probably also have to consider calculated columns in other tables.

The other option here is that you could post a few rows of sample data for us. Then explain the type of output you are trying to achieve (based on that sample data) and we can possibly suggest a way of building a measure for this rather than a column.

[fuelgun · Accepted solution]

I just figured out a solution.  Instead of using LOOKUPVALUE, I used RELATED instead.  This allowed me to keep bi-directional cross filtering in the relationship