Circular Dependency in DAX caused by CALENDARAUTO

Working on a small demo, I was surprised by an unexpected circular dependency.

I built a repro to understand what was going on and I’m writing this post to fix what I found out for future memory.

The model is very simple, I have a table Employee with the Name and the Hire and Leave dates

I’ve added a Date calculated table, created by CALENDARAUTO

Then I generated a Employee Snapshot table, with all the dates between the Hire and Leave dates for each employee.

When I tried to create the relationship on the Name columns between the Employee and the Employee Snapshot tables, a Circular Dependency error was generated.

Trying the known techniques to solve the circular dependency on the Employee Snapshot DAX code didn’t work.

Eventually, the only solution I found was to replace the CALENDARAUTO with CALENDAR.

The Employee table in the repro is very simple, I created it directly with “Enter Data” button.

SNAG-0008

The code to create the Date table with CALENDARAUTO is straightforward

Date = CALENDARAUTO()

The code to create the Employee Snapshot, reduced to avoid context transitions and Time Intelligence function is

Employee Snapshot = 
SELECTCOLUMNS( 
    GENERATE(
        ALLNOBLANKROW( Employee ),
        FILTER (
            ALLNOBLANKROW( 'Date'[Date] ),
            'Date'[Date] >= 'Employee'[Hire Date] &&
            'Date'[Date] <= 'Employee'[Leave Date]
        )
    ),
    "Name", Employee[Name],
    "Date", 'Date'[Date]
)
The model now has the three tables, without any relationship
 
SNAG-0007
 
Then, when I try to create a relationship using the Name column
 
Relationship on Name setting
 
The circular dependency error is generated
SNAG-0006
The solution I found is to replace the code to generate the Date table
Date = 
VAR MaxEmployeeDate = MAX( MAX( Employee[Hire Date] ), MAX( Employee[Leave Date] ) )
VAR MinEmployeeDate = MIN( MIN( Employee[Hire Date] ), MIN( Employee[Leave Date] ) )
VAR MaxDate = DATE( YEAR( MaxEmployeeDate ), 12, 31 )
VAR MinDate = DATE( YEAR( MinEmployeeDate ), 1, 1 ) 
RETURN
    CALENDAR( MinDate, MaxDate )
With the new Date table it’s now possible to create the relationships
 
SNAG-0010
 
The explanation is that CALENDARAUTO is using all the dates in the model, including those in the calculated table.

 

The sample pbix can be downloaded from github

 

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s