Circular Dependencies in DAX

What is a circular dependency? If A depends on B and B depends on A, then you have a circular dependency. As programmers, we know that they should be avoided to obtain a working system. All this looks fine but, when authoring a simple DAX formula containing CALCULATE, SSDT (formerly known as BIDS, sometimes called Visual Studio… I love Microsoft attitude to find new names) warned me about circular dependency errors and, believe me, there seemed to be no loops inside my formulas.

After some fight with the tool, it turned out that anytime you use CALCULATE in a formula for a calculated column you are in danger for a circular dependency and, to solve the issue, the easiest way is to add a key column to a table. Understanding why the problem exists is not trivial and it requires you to read the article I have published on the SQLBI website here:

Clearly the scenario is the same for PowerPivot 2012 and for Tabular in SSAS 2012.

Happy reading! And, as always, comments are very welcome.

BTW: Happy new year to everybody! In case you forgot it, THIS is the year of SQL2012. Sorriso