Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Circular dependency using Calculate

For a calculation I have created two column measures. The first calculates the # empty values in a row, the second calculates # values in the row that are not labelled as "N/A". However when trying to calculate these two measures at the same time I get an error saying that circular dependency is found. I think the reason is because I do two calculations on the same Table.

 

1st column measure:

empty count = CALCULATE(COUNTROWS(Sorted),Sorted[Value]="")
 
2nd column measure:
totalcount = CALCULATE(COUNTROWS(RELATEDTABLE(Sorted))-CALCULATE(COUNTROWS(Sorted),FILTER(Sorted,Sorted[Value]="N/A")))
 
How do I calculate this without the circular dependency?
 
The background is that I want to calculate these per row for variables in Table A [200 x 200 entries] and to make this calculation in the first place I made a pivot table (called Sorted) to make this calculation in the first place. Using measures (not column measures) I can not visualize it in a table due to the 1:n relationship, resulting in a n : n visualization.
Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Sorry, it took a while to rebuild my .pbix file to allow for posting in this thread.

       

      There are two table visualizations in the PBI Dashboard. My goal is to have the 2 columns (Expected Result and Empty Information Fields [Alphabeticaly] in the same visualization and ordered per Variable (1 time per Variable). Now when I add both columns to the same visual the table shows each Variable a number of n times, n being the number of Empty Information Fields.

       

      To obtain this I have tried adding colum measure NewTable[empty count] and NewTable[totalcount]. However, having both these measures introduces the circular dependency. 

       

      Link to .pbix file 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The easiest way is to make the calculations in Power Query. If you want to remove the circ dep in DAX, avoid CALCULATE, which effects contex transition (placing filters on all columns in the table), and rely only on FILTER.