topic Re: A circular dependency was detected error on returning value from a table in DAX Commands and Tips

A circular dependency was detected error on returning value from a table

abdmajidnor — Sun, 12 Dec 2021 04:24:06 GMT

Hi, I'm new to PowerBI. I'm currently calculating using DAX to return the latest data from a table base on its ID, UniqueID and latest date .

At first, everything was fine, I managed to return a value from other table. As I proceed to other column, There's an error message about Circular Dependency. Is there any way to avoid the error? Here's the sample code from my data.

C34 = VAR LastDayC34 = CALCULATE ( MAX ( 'TX - Score Table'[Created] ), NOT ISBLANK ( 'TX - Score Table'[C34] ) ) VAR LastUniqueIDC34 = CALCULATE( MAX('TX - Score Table'[ID]), NOT ISBLANK('TX - Score Table'[C34])) VAR LastIdC34 = CALCULATE ( MAX ( 'TX - Score Table'[tag_id]), 'TX - Score Table'[Created] = LastDayC34, 'TX - Score Table'[ID] = LastUniqueIDC34, NOT ISBLANK ( 'TX - Score Table'[C34] ) ) RETURN CALCULATE (MAX('TX - Score Table'[C34]), 'TX - Score Table'[tag_id] = LastIdC34,'TX - Score Table'[ID]=LastUniqueIDC34)

C41 = VAR LastDayC41 = CALCULATE ( MAX ( 'TX - Score Table'[Created] ), NOT ISBLANK ( 'TX - Score Table'[C41] ) ) VAR LastUniqueIDC41 = CALCULATE( MAX('TX - Score Table'[ID]), NOT ISBLANK('TX - Score Table'[C41])) VAR LastIdC41 = CALCULATE ( MAX ( 'TX - Score Table'[tag_id]), 'TX - Score Table'[Created] = LastDayC41, 'TX - Score Table'[ID] = LastUniqueIDC41, NOT ISBLANK ( 'TX - Score Table'[C41] ) ) RETURN CALCULATE (MAX('TX - Score Table'[C41]), 'TX - Score Table'[tag_id] = LastIdC41,'TX - Score Table'[ID]=LastUniqueIDC41)

I tried using other formula such as FILTER and MAXX but it was not sucessful.

Thank you for the Tips and Advice 😉

Re: A circular dependency was detected error on returning value from a table

bcdobbs — Sun, 12 Dec 2021 07:07:25 GMT

Are these calculated columns? If so what table are they being built in?

Can you provide a screen shot of the model?

Re: A circular dependency was detected error on returning value from a table

ValtteriN — Sun, 12 Dec 2021 10:24:13 GMT

Hi,

The problem you are describing seems to be that you have two calculated columns containing calculate. This will cause issues due to context transition. This post by SQLBI explains this marvelously: "https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/"

The solution to your issue is to use ALLEXCEPT() within your CALCULATE.

I hope this helps to solve your issue and if it does consider accepting this as a solution!

Re: A circular dependency was detected error on returning value from a table

abdmajidnor — Mon, 13 Dec 2021 05:10:04 GMT

Here's my sample data, errors on circular dependency and desired value.

Re: A circular dependency was detected error on returning value from a table

Anonymous — Thu, 16 Dec 2021 06:06:52 GMT

Hi abdmajidnor,

Did ValtteriN 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: A circular dependency was detected error on returning value from a table

polymathy2017 — Thu, 24 Oct 2024 04:16:38 GMT

Definitely didn't solve it. SQLBI did, as they so often do, but asking for kudos for a link is hardly a solution.

Here are a couple of links that actually work:
https://www.youtube.com/watch?v=m-qhcDcNKrE&ab_channel=SQLBI
Avoiding circular dependency errors in DAX - SQLBI

Definitely not asking to be marked as a solution since all I did was point those interested to the actual solution.