Circular dependency using Calculate

[Anonymous]

For a calculation I have created two column measures. The first (NewTable[empty count]) calculates the # empty values in a row, the second (NewTable[totalcount] 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.

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. How do I calculate this without the circular dependency?

Link to .pbix file 

[Anonymous · Accepted solution]

Hi @Anonymous,

In fact, this is a common issue appears when you duplicate reference across multiple calculated fields.

I'd like to suggest you measure expression instead calculate columns, you can check the below link about the difference between calculate column and measure:

Calculated Columns and Measures in DAX - SQLBI

In addition, you can also take a look at the following blog that mention the issue to know more about this sceariao:

Avoiding circular dependency errors in DAX - SQLBI
Regards,
Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

First, you should do such calculations in Power Query because this is the right tool for data transformations (DAX is a data analysis expression language). Second, you are getting this circular dependency error because you're using CALCULATE (directly or indirectly) in the calculated columns (by the way, there's no such thing like "column measures", either measures or calculated columns) and due to how CALCULATE works it's unavoidable. Third, you can (but you shouldn't) do the same calculations without any CALCULATE since in the row you've got all you need - a row context.