Forum Discussion
Circular dependency was detected
Hi there,
I am trying to create records for 35+ days, 60+ days, 90+ days
- In my fact table I have mutilple dates which are duplicate.
- I have created a Calendar table with condition Calendar (min (date), Max(date)).
- Next I wanted to create a one more table by using calendar table. I am not able to create a relationship between them.
1)Calendar table Logic :
2)Condtional table logic to create a bucket for 35+ days,60+ days, 90+ days records.
3) In model View I have created many to one relation with my Fact table and calendar table.
I wanted to create a relation with Calendar table and Period table, In which PowerBI is throwing an error called circular dependency was detected.
How can i solve this? Thanks in advance for your help
7 Replies
jgeddesSuper User
You cannot create a relationship between the calendar table and the period table since it appears you used the calendar table to construct the period table.
You can create a calculated column in your calendar table using a switch statement to create your periods.It could look something like...
Period = SWITCH( TRUE(), DATEDIFF(Calendar[Date], UTCNOW(), DAY) > 90, "90+ Days", DATEDIFF(Calendar[Date], UTCNOW(), DAY) >= 60, "60-90 Days", DATEDIFF(Calendar[Date], UTCNOW(), DAY) >= 35, "35-59 Days", "Current" )I understand, Thanks for the response.
However this is not our business requirement.
Our Client Business requirement is to show records which are
35+ days ( Include all records which missed target > 35 days)
60+ days ( Include all records which missed target > 60 days)
90+ days ( Include all records which missed target > 90 days)
It means 35+ has more records than 60+ days & 60+ days has more records than 90+ days.
Example:
2024-03-24 : This record will fall under categorys like 35+
2024-02-24 : This record will fall under all categorys like 35+ & 60+
2023-11-24 : This record will fall under all categorys like 35+, 60+, 90+
--------------------------------------------------------------
The similar logic works if days are inbetween, PBI doesnt throw error
Period =
VAR
_Last30days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-30,UTCTODAY())
)
,"In the last","30 days"
)
VAR
_Last60days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-60,UTCTODAY())
)
,"In the last","60 days"
)
VAR
_Last90days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-90,UTCTODAY())
)
,"In the last","90 days"
)
RETURN UNION(_Last30days,_Last60days,_Last90days)
Reference Video: How to create 30/60/90/Lifetime filter in Power BI (youtube.com)- jgeddes
Ok, I understand your requirement.
If you need to create a second date table with the periods you cannot refer to the first calender table.
Something like this might work for you.Table 2 = var _vCalendar = CALENDAR(MIN(tbl[Target Date]), MAX(tbl[Target Date])) var _35 = ADDCOLUMNS( FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >35), "Period", "35+ Days" ) var _60 = ADDCOLUMNS( FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >60), "Period", "60+ Days" ) var _90 = ADDCOLUMNS( FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >90), "Period", "90+ Days" ) RETURN UNION(_35,_60,_90)Just mimic the code in your calendar table in the _vCalendar variable. This should allow for the relationship to be built between the two date tables.
Thanks for the response, The provided solution is not working.
