Circular data error in the workday training video

Power BI
1

Hi, My first post looking for help

Im following the training video on how to input ranking for workdays yet i get an error of a A circular dependency was detected: Dates[Column].

This is the code i added to a column in the table. Whats even more annoying i got it working the first time i did it however i didnt save my model and now when i try and do it again i get this error how do i fix it?

Workday Number = 
VAR CurrentMonth = Dates[Month & Year]
VAR MonthTable = FILTER( ALL( Dates ), Dates[Day Type] = "Workday" && Dates[Month & Year] = CurrentMonth )

RETURN
IF( Dates[Day Type] = "Weekend",
    BLANK(),
        RANKX( MonthTable, CALCULATE( AVERAGE( Dates[DayOfMonth] ) ), , ASC ))
2

Hi there,

The formula looks fine to me, so all I can think of is there is an issue with the table you’re placing it into.

Are you definitely adding this to the date table in your model? And do you have all of the columns that are reference in the example file here?

All I can think of is that something isn’t the same here and that’s most likely why you’re facing an issue like this.

Some image of what you are seeing would be helpful if you can’t solve it with this info.

Thanks
Sam

3

HI,

Yes its going into the model.

One thing i noticed in your video the column header names are different to the file you provide and from your example PBIX file there are some query editor changes you have made. either way i have tried all combos but cant get it to go.

Column thats different is the Monthnyear i think you call it Month & year in your video ? which makes sense and i dont see how that creates a circular reference anyway.

Like i say whats really annoying me is i did get it to work once but cant repeat it!!! wierd

Dan

4

@Krays23
Can you upload your sample where you are getting this error? Also, being a date table have you set it as such?

Setting the date table as a date table ( or creating a 1:M relationship) will tell DAX to only use that unique column and not all the columns of the table.

Just an idea on why you may be getting the error only sometimes.

Enterprise%20DNA%20Expert%20-%20Small

6

HI Nick,

Marked it as a date table and it worked it also works if i create a 1 to M relationship before i create the column id assume.

Thanks for your help

SAM Material and Production lead times Power BI Tool.pbix (2.1 MB)

7

Thanks All, Solved it by marking it as a date table or creating a relationship prior to creating the extra columns

Thanks

Dan

8

I did a quick review of your table and to me it’s missing the day of the month column, so it doesn’t have all the inputs needed for this formula to work?

image
image938×245 17.5 KB

The other things you’ve mentioned like setting it as a date table and having a relationship shouldn’t really matter for this at all.

Sam