Circular dependency calculated table

[Berl21]

Hi,

I created a calculated table with the following command:

Leads Amount PY =

CALCULATETABLE(

SUMMARIZE(

    Leads_Table,

     Leads_Table[ZH_Account__c],

     Leads_Table[Lead_Creation_Date],

    "Count of Leads PY",

    [Count Leads]

),

YEAR ( Datetable[Date]) = YEAR ( TODAY () ) -1

)

When I try to add it in the model, I get the following mistake:

I am connecting Lead_Creation_Date to the column "Date" in my calendar table. There are no other link between the two tables. What are the typical scenarios I should double check?

Thanks,

Pauline.

[v-yalanwu-msft]

Hi, @Berl21 ;

Is your problem solved?  If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Berl21 ;

You could add a dax ALLNOBLANKROW() such as :

Leads Amount PY =
CALCULATETABLE (
    SUMMARIZE (
        ALLNOBLANKROW ( Leads_Table ),
        Leads_Table[ZH_Account__c],
        Leads_Table[Lead_Creation_Date],
        "Count of Leads PY", [Count Leads]
    ),
    YEAR ( Datetable[Date] )
        = YEAR ( TODAY () ) - 1
)

You could refer this video.

Avoiding circular dependency errors in DAX - YouTube
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@Berl21,

Here are two ideas:

1. Move the calculated table to Power Query.

2. Modify the DAX as follows:

Leads Amount PY =
CALCULATETABLE (
    SUMMARIZE (
        Leads_Table,
        Leads_Table[ZH_Account__c],
        Leads_Table[Lead_Creation_Date],
        "Count of Leads PY", [Count Leads]
    ),
    FILTER ( ALLNOBLANKROW ( Datetable[Date] ), YEAR ( Datetable[Date] ) )
        = YEAR ( TODAY () ) - 1
)

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/