cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Circular dependency when sorting by column

Hi,

 

I would like to sort my month names by a calculated column based on the previous 12 month period. I have got the values populating in a suitable way using relative months to the current month.

 

MaxRelativeMonth = CALCULATE(MAX('Calendar'[RelativeMonth]),FILTER('Calendar','Calendar'[Monthly Reporting Period]="TY" && 'Calendar'[Month Name]='Month Ordering'[Month Name]))

 

reldate.PNG

 

However this keeps resulting in circular dependency errors. Any help?

 

Thanks,

Adam

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@AdamRobC , As you are using month name in creating the sort column you can not use that as a sort column

 

Create a column

Month Name 1 =[Month Name]

 

MaxRelativeMonth = CALCULATE(MAX('Calendar'[RelativeMonth]),FILTER('Calendar','Calendar'[Monthly Reporting Period]="TY" && 'Calendar'[Month Name]='Month Ordering'[Month Name]))

 

Sort Month Name 1 on the new sort column and use that is the visual. Do rename as per need



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

@AdamRobC , As you are using month name in creating the sort column you can not use that as a sort column

 

Create a column

Month Name 1 =[Month Name]

 

MaxRelativeMonth = CALCULATE(MAX('Calendar'[RelativeMonth]),FILTER('Calendar','Calendar'[Monthly Reporting Period]="TY" && 'Calendar'[Month Name]='Month Ordering'[Month Name]))

 

Sort Month Name 1 on the new sort column and use that is the visual. Do rename as per need



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak thanks, that worked 🙂

Super User III
Super User III

@AdamRobC 

Ok, but you are using the Calendar table in the calculation as well.

Can you create that sorting column in Power query instead? That should avoid the issue.

Otherwise I'd need to see the calendar table its columns) and the its relationship with 'Month ordering'. If you can share the pbix, much better

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

 

Super User III
Super User III

Hi @AdamRobC 

When do you get the circular dependency error, when creating the calc column or when trying to "Sort by"?

Can you show the full table (columns)?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB The error occurs when trying to sort Month Name by MaxRelativeMonth. That is the entire table, there are only 3 columns.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.