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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Lori001
Frequent Visitor

Dax circular dependency error on calculated column

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

1 ACCEPTED SOLUTION
v-jianpeng-msft
Community Support
Community Support

Hi, @Lori001 

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

vjianpengmsft_0-1710400901956.png

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:

vjianpengmsft_1-1710401740973.png

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.

 

View solution in original post

4 REPLIES 4
JamesKim
Regular Visitor

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

v-jianpeng-msft
Community Support
Community Support

Hi, @Lori001 

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

vjianpengmsft_0-1710400901956.png

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:

vjianpengmsft_1-1710401740973.png

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.

 

Hi Govind,

 

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

govindarajan_d
Solution Supplier
Solution Supplier

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. 

govindarajan_d_0-1710260864662.png

 

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

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors