Circular Dependency with Date Tables

[Anonymous]

Hi,

I'm trying to create a couple calculated columns within a date table for use in other tables. Namely, I have an "IsWorkDay" column (boolean) and an "IsHoliday" column (boolean), which is calculated from a linked holiday table. I need an additional column that gives the total work days in a month (the sum of all days that are a work day and not a holiday) for each day because of the relationships I use for the rest of my file, but I'm getting a circular dependency error in my "IsWorkDay" column. If I remove the reference to the "IsHoliday" column in the "IsWorkDay" column, the other calculated columns work fine. This seems off to me because the link to the holiday table should only be used by the IsHoliday column, so I'm not sure where the circular dependency is. I tried reading up on the circular dependency articles linked to throughout the forum, but I'm not quite getting it. I've attached a link to my file for demonstration.

PBIX

[TeigeGao]

Hi @Anonymous ,

It's sorry that I can't open your shared link, could you please share it via other network storage? According to your description, it looks like that your "IsWorkDay" write based on the "IsHoliday".

Best Regards,

Teige

[Anonymous]

@TeigeGao 

Sorry for the bad link, please try this one:

@parry2k 

I have read through the two articles you shared and I believe I understand the gist of what they're saying. Given what I'm trying to accomplish in my model, can you recommend a workaround for the circular dependency?

[parry2k]

@Anonymous not sure why need IsHoliday column, if you are adding that just for calculating IsWorkDay then you can get rid of IsHoliday column and add the same logic in IsWorkDay Column.

In case you need IsHoliday column then use same logic in IsWorkDay column instead of referring to IsHoliday

[Anonymous]

@parry2k 

I need the IsHoliday column because I'm going to use that in a measure in my fact table, and I need the IsWorkDay for a calculated column in this date table. To give the full picture, my fact table has all of my data on a monthly basis, but I force the dates in that table to the first of every month so that I can still link all of my dimensions to the date table and filter on a monthly basis through that. That being the case, I use calculations to sum up the number of total days and working days (i.e., non weekends/non holidays) for measures in my fact table. I need the total days in a month and working days in a month in a column for each day so that data is captured on each row in the date table because of the fact that all my fact table dates fall on x/1/2019.

Also, I tried copying the logic of IsHoliday into IsWorkingDay to avoid the reference but I'm still getting the circular dependency.

There's probably a more elegant way to do what I'm trying to do, but the model I have has worked up to this point until I realized that I am double counting holidays that land on weekends in some of my calculations.

[parry2k]

@Anonymous well if you need both column I mentioned that you use same IsHoliday logic in IsWorkingDay instead of using IsHoliday column in IsWorkignDay?

Did you tried this? Let's focus on this if this get solved then talk on next steps. I like to break things into smaller pieces.

[Anonymous]

@parry2k 

Yes, I tried copying the formula of IsHoliday into IsWorkingDay and it still gave me the error.

[parry2k]

@Anonymous ok, I didn't looked at your file yet thou. I'm wondering why not you merge both tables in PowerQuery and then IsHoliday just part of date table.

[Anonymous]

@parry2k 

If I merged them, how would that look? The holiday table is just a 2-column table with a few lines that show our company's holiday schedule, whereas my date table is a whole bunch of columns with month number, month name, quarter, etc.

[parry2k · Accepted solution]

@Anonymous attached. I added two columns in PowerQuery, feel free to change as per your need.

[Anonymous]

@parry2k 

I think that'll do it. Thanks for all the help!

[parry2k]

@Anonymous did you read this

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

https://www.sqlbi.com/articles/understanding-circular-dependencies/