Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Circular Dependency with Date Tables

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

1 ACCEPTED SOLUTION

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

11 REPLIES 11
TeigeGao
Solution Sage
Solution Sage

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
Not applicable

@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?

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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.

@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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k 

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

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k 

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

parry2k
Super User
Super User

@Anonymous did you read this

 

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

 

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.