Error: A circular dependency was detected

[drew989898]

Hi,

I'm hoping to get some help on a problem I'm having with a circular dependency. I understand why I'm getting this error, but I'm not sure how to work around it to get the result I require. I have created a sample .pbix file replicating the table and the two attempts shown in this post... but I have no way of attaching it here 😞

What I need to do is compare the start and end date-times of each item in the Type column against the start and end date-times of the Type availability. These all exist in the same column of the query table (generated with power query).

I created a calculated table using FILTER on Type = Availability. If I create a visual table and add the relevant columns I get something like the sample data below - the first row is just to make it clear here which table each column is coming from.

Query Table	Query Table	Query Table	Query Table	Query Table	Calculated Table	Calculated Table	Calculated Table
Date	Emp ID	Type	Start Date-Time	End Date-Time	Type 2	Start Date-Time 2	End Date-Time 2	Revised Start Date-Time	Revised End Date-Time
1/9/2025	123	Availability	9am	4pm	Availability	9am	4pm	9am	4pm
1/9/2025	123	Activity 1	8am	9am	Availability	9am	4pm	9am	9am
1/9/2025	123	Activity 2	10am	2pm	Availability	9am	4pm	10am	2pm
1/9/2025	123	Leave	4pm	6pm	Availability	9am	4pm	4pm	4pm
1/9/2025	124	Availability	9am	1pm	Availability	9am	1pm	9am	1pm
1/9/2025	124	Activity 1	12pm	2pm	Availability	9am	1pm	12pm	1pm

What I need to do is compare Start Time with Start Time 2 to obtain the last two "revised" columns of the table, for instance like this:

Revised Start Date Within Availability Window old 2 =
IF (
'Query Table'[Start Date-Time]
< RELATED ( 'Calculated Table'[Start Date-Time] ),
RELATED ( 'Calculated Table'[Start Date-Time] ),
'Query Table'[Start Date-Time]
)

I also tried something like this after reading the Marco Russo and Alberto Ferrari article on circular dependency with calculated tables (https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/😞

Revised Start Date Within Availability Window =
VAR table1 = IF (
HASONEVALUE ( 'Query Table'[Start Date-Time] ),
DISTINCT ( 'Query Table'[Start Date-Time] )
)

VAR table2 = IF (
HASONEVALUE ( 'Calculated Table'[Start Date-Time] ),
DISTINCT ( 'Calculated Table'[Start Date-Time] )
)

RETURN IF (table1 < table2, 1, 0)

The idea is that if start time is < start time 2 (Availability start time) then return availability start time instead, otherwise return start time. But I'd be happy just to return 1 and 0 to see that it works, hence why in the last code example I tried just that. It's the comparision that triggers the error.

In each case I get the circular dependency error, obviously because I'm essentially trying to compare the same object (start time) with itself.
I know I could do this with power query by duplicating (NOT referencing) the original table, grouping on availability then merging this back to the original table. I want to do this via DAX, not power query, for a few reasons:
1. This is a fact table, and it's quite large as it is with around 2 million rows.
2. There is already a fair amound of data manipulation, so to duplicate the table will slow the refreshes.

3. I will be creating direct query connections to this model for multiple reports, and this operation is only required for one report. Therefore it doesn't make sense to load this duplicate table permanently to the model. It's better to load it to DAX so it's only created at run time when it's required.

I'd really appreciate if someone had a solution to how I can compare these columns without the circular dependency error!
Thanks
Drew.

[Gabry]

Hey @drew989898 ,

Let me clarify:

• If you use CALCULATETABLE inside a measure, it will be calculated on the fly, no data will be written to the model.

• If you use CALCULATETABLE to create a new table in the model, then of course the data will be stored.

My PBIX works as expected. The start date calulated isn't always the same.

You mentioned: “if the current start time is less than the start time of type availability, then take the start time of availability, otherwise take the current start time.”
That’s exactly what I did in the file.

If instead you want the opposite logic, you just need to switch the IF statement.

Did you check both the measure I built and the calculated columns?

Look at my output

It's exactly aa the table you provided. I re-attached in the previous post the PBIX with the IF statement switched. Take a closer look and let me know. 😉

Ps. I made the measure just for start date just to let you see how it works. difference measure is your Revised Start Date-Time

[Gabry · Accepted solution]

Hey buddy,

I’m not completely sure I understood all the requirements. Assuming you have a key on emp id, I created a file for you, please take a look and let me know.
I should mention that if you create a table using CALCULATETABLE, the data will actually be stored in the model. That makes me wonder if maybe you’d prefer to do it using only a measure instead.

EDIT: I updated the file, I put also a measure to do the calculation you need. Let me know if this solve your issue

[drew989898]

Hi Gabry,
This is exactly the solution.
Previously I was getting this, which is only returning the initial start time:

Now I can see exactly what I need to! As you say, difference measure is the revised start time now 🙂

It seems that lookupvalue is the game changer doing what I was previously trying to do with a calculated table, instead via a calculated column which is much better. I will need to look into this much more.

Thanks so much!
Drew

[Gabry]

Cool, I'm happy to hear that!
Keep in mind that the LOOKUP DAX function isn't supported when the table is in DirectQuery mode. Also, it's considered best practice not to add calculated columns to DirectQuery tables. If you plan to use the source table in DirectQuery mode, it's better to use a measure instead.

[drew989898]

No worries. This is easy enough to convert to a measure using MAX(), which seems to work well enough.

Start date-Time availability2 = LOOKUPVALUE('Table'[Start Date-Time],'Table'[Emp ID],MAX('Table'[Emp ID]), 'Table'[Type], "Availability")

So I think I can forge ahead with this solution!

If I were able to do some of this ETL stuff with SQL that would be great. But I'm limited to power query and DAX.
Thanks again, Drew.

[drew989898]

Hi Gabry,

Thanks for replying.

I didn't know a calculated table was loaded into the model the same way power query tables are. Ideally this could be done with calculated columns, but I don't know how to group by to achieve this result.

I had a look at your pbix but it appears to be giving the same start/end date-times. Then I realised I didn't really make it clear in my example table so I have edited my original post to make it clearer. I've added 2 columns to represent the desired outcome - yes, these could very well be calculated columns. And I've edited some of the fields to better represent the situation.

Row 1: Revised times are unchanged.
Row 2: Start time is revised to equal start time of availability for that employee
Row 1: Revised times are unchanged.
Row 4: End time is revised to equal End time of availability for that employee. This makes start = end time so duration is 0.
Row 1: Revised times are unchanged.
Row 1: End time is revised to equal End time of availability for that employee. 

Something like this would work (two calc columns one for start another for end), but I'm unable to get GROUPBY to work.... ever 😄

RevisedStartDateTime =
GROUPBY(Table1,
Table1[Date],
Table1[Emp ID],
"new start date-time", CALCULATE(MIN('Table1'[Start Date-Time]), Table1[Type] = "Availability")
)

The error tells me I need to aggregate over CURRENTGROUP() but I'm not sure what that means and whether it will even work in this situation. So that is how I ended up with the calculated table solution, which got me a little further. Until I arrived at the circularity error.

Thanks for your help,
Drew.