Forum Discussion
Error: A circular dependency was detected
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
Super UserHey 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
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.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.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.
