Dax circular dependency error on calculated column

[Lori001]

Hi,

I am getting a circular dependency error and not sure how to solve it.

I have a source table (General) with a column "Amount". This amount can be incl Tax or excl Tax depending on the document type.

The source table has also a column "Tax amount".

I have to make three Dax calculated columns as follow. It goes well until my third dax code. There I get a circular dependency error:

1)

Amount excl tax = IF('General'[Document type] = "Order", 'General'[Amount incl tax] - 'General'[Tax amount],
IF('General'[Document type] = "invoice", - 'General'[Tax amount]))

2)
Tax amount = 'General'[Tax] / 10

3)
Amount incl tax = IF('General'[Document type] = "Order", 'General'[Amount] + 'General'[Tax amount],
IF('General'[Document type] = "invoice", 'General'[Amount excl tax] + 'General'[Tax amount]))

Error: A circular dependency was detected: General[Amount excl tax], General[Amount incl tax], General[Amount excl tax]

Hope someone can help to see what I can do to solve the third query (or above queries to make the third query work.

Thanks, Lori

[JamesKim]

Please install previous version(2024-02). It will be no issue at all.

[v-jianpeng-msft · Accepted solution]

Hi, @Lori001 

Thanks @govindarajan_d , I have the following additions. Based on your description and my testing, there is indeed a dependency:

As @govindarajan_d  said, your Amount excl tax column and Amount incl tax column depend on each other. Circular dependencies in Power BI can occur when two or more objects reference each other in a way that Power BI can't handle. Here are some common scenarios and solutions:
1. Calculated columns that reference each other: If you create two calculated columns that reference each other, a circular dependency is generated. For example, if the Line Margins are calculated from Discount PCT and the Discounted PCT is calculated from Line Margins, there is a circular dependency. The solution is to rewrite the code.
2. Context transformation within a calculated column: If you use COMPUTE in a calculated column, it will perform a context transformation and make the column dependent on all the columns in the table. If there are two such columns, they depend on each other, resulting in a circular dependency. The solution is to use ALLEXCEPT or REMOVEFILTERS and keep only the primary key of the table to limit the list of columns that the calculated column depends.
3. Create relationships that involve calculated columns or tables: This can also lead to hidden circular dependencies.

You can click on the links below to learn more about why circular dependencies appear and why they don't work, as well as solutions to these errors:

Avoiding circular dependency errors in DAX - SQLBI

Understanding circular dependencies in DAX - SQLBI

https://www.youtube.com/watch?v=OFwspc_C5Xg&ab_channel=AsanTutorials

Based on the DAX you provided, I rewrote your logic to avoid circular dependencies, and here are the new DAX expressions:

Amount excl tax =
IF (
    'General'[Document type] = "Order",
    'General'[Amount] + 'General'[Tax amount] - 'General'[Tax amount],
    IF ( 'General'[Document type] = "invoice", - 'General'[Tax amount] )
)

Amount incl tax =
IF (
    'General'[Document type] = "Order",
    'General'[Amount] + 'General'[Tax amount],
    IF (
        'General'[Document type] = "invoice",
        'General'[Amount excl tax] + 'General'[Tax amount]
    )
)

Here are the results:

Since the calculation column of Amount incl tax is 'General'[Amount] + 'General'[Tax amount] when 'General'[Document type] = "Order", the above operation is performed directly in the Amount excl tax calculation column without relying on Amount incl tax. 

In the Amount excl tax calculation column, if 'General'[Document type] = 'Order', then 'General'[Amount] + 'General'[Tax amount] - 'General'[Tax amount] is executed directly.

I've provided the PBIX file below for this time, and it would be great if it would be helpful to you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lori001]

Hi Govind,

Thank you very much for this reply. It has helped!

[govindarajan_d]

Hi @Lori001,

It seems in the 1st measure you refer the 3rd measure name, while in the 3rd measure, you refer the 1st measure name. 

Do you think you can break down one of the measure instead of directly using the measure name?