Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
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 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.
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.
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.
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 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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
104 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
98 | |
81 | |
61 | |
55 |