Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dpbi
Helper I
Helper I

Circular Dependency

Hi all. I need some help with the following:


A measure works fine when creating first calculated column.

When i'm using similar measures (but not the same) for the second and the third calculated columns, i get 'circular dependency' error. 

The calculation of each calculated column is not depended on each other.

 

Circular1.PNG

 

My sample table ("Temp") contains two columns: "Index" and "Rate".
I need to count the number of occurrences of each value ("Low" , "Med" , "Hige") in a range of 5 rows (for each row in the table).

For example,  the first row in the calculated column "High Count" shows how many times the value "High" appear in the range of rows 1 -5,  the second row shows the same calculatiion but for the rows 2 -6.

I use a measure to define each calculated column. For example, the measure for the "High Count" calculated column:


High Measure =
VAR RangeStart = CALCULATE ( MIN ( Temp[Index] ))
VAR RangeEnd = RangeStart + 4
RETURN
COUNTROWS (

        FILTER ( ALL(Temp) , Temp[Index] >= RangeStart  &&  Temp[Index] <= RangeEnd  &&  Temp[Rate] = "High" ) )

 

When i'm trying to define the second caclulated column with the second measure (shown below) i get the circular dependency error. 

 

Med Measure =
VAR RangeStart = CALCULATE ( MIN ( Temp[Index] ))
VAR RangeEnd = RangeStart + 4
RETURN
COUNTROWS (

        FILTER ( ALL(Temp) , Temp[Index] >= RangeStart  &&  Temp[Index] <= RangeEnd  &&  Temp[Rate] = "Med" ) )

                         

Any idea for a solution? 

Thanks,

1 ACCEPTED SOLUTION

I used COUNTAX instead of COUNTROWS and it solved the problem.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @dpbi,

 

The short answer is: change the calculated columns by measures with the same code that you have and should work.

 

columns_vs_measures.png

 

The long answer is very long, it is caused by the internal behavior of the calculated columns. In the following link you will find how to understand the circular dependencies and how to avoid them:
https://www.sqlbi.com/articles/understanding-circular-dependencies/

 

Regards.

Hi @Anonymous

Thank you very much for your reply, but it doesn't solve the problem.

I'm aware that i can drag the base columns and the measures to a visual and see the results, but it is not what i need.

 

I need to have the results in calculated columns (since i need the results for each row). Only then  i can manipulate the data based on the results. The sample table, is a sample table with few rows where the real dataset is much bigger.

 

I couldn't find a solution in the SQLB article you mentioned. My table is the only table and Iit is not part of a model. The table has only two columns. The 'Index' column has unique identifier (but maybe not in SQL terms).

 

My sample table is the same as the source file (Excel). 

Is there a way in DAX or Power Query to create these calculated columns?

I used COUNTAX instead of COUNTROWS and it solved the problem.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.