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

[abdmajidnor]

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 😉

[Anonymous]

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

[ValtteriN · Accepted solution]

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!

[polymathy2017]

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. 

[bcdobbs]

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

Can you provide a screen shot of the model?

[abdmajidnor]

Here's my sample data, errors on circular dependency and desired value. 
Desired DataTable 1Dependency Error