cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fuelgun Frequent Visitor
Frequent Visitor

=MEDIAN() results in circular dependency

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]

1 ACCEPTED SOLUTION

Accepted Solutions
fuelgun Frequent Visitor
Frequent Visitor

Re: =MEDIAN() results in circular dependency

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

View solution in original post

4 REPLIES 4
Super User
Super User

Re: =MEDIAN() results in circular dependency

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 Frequent Visitor
Frequent Visitor

Re: =MEDIAN() results in circular dependency

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

 

 

Super User
Super User

Re: =MEDIAN() results in circular dependency


@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 Frequent Visitor
Frequent Visitor

Re: =MEDIAN() results in circular dependency

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 347 members 3,206 guests
Please welcome our newest community members: