Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello data wizards,
I have this problem in Power BI desktop. I need to sort months in a matrix according to their year. The months go like this:
Jan, Feb, Mar, Apr, May, Dec
the December here is from the previous year, so I want:
Dec, Jan, Feb, Mar, Apr, May
I have created a calculated column that goes like columnForSort = FORMAT(tablename[DateCol]; "YYYYMM") which I want to use for sorting, because there the dates look like this: 201912, 202001, 20202, etc.
Perfect, but now when I click on the Date column in the fields tab and then I "sort by column" and choose the new columnForSort it loads for a bit and then an error pops up that reads:
Failed to save modifications to a server. Error: Cyclic dependency detected: tablename[Date], tablename[columnForSort], tablename[Date].
I guess the problem is with sorting an original column by a new column that was created in accordance to the original column.
Is there a way to work around this?
THANK YOU
@zidek22 , I am not sure How you have used it.
But You can not sort a derived column on the base column
This how we create a column and its sort column
Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")
Month= FORMAT([Date],"mmm")
Month sort = FORMAT([Date],"mm")
Both coming from same column, Not A-> b-> C
Thank you for your reply @amitchandak !
I have a base column and I made one derived column. Then I highlight the base column and click "sort by column" and choose the derived column. I've already done this in a different project where I needed to sort months chronologicaly and not alphabeticaly and it worked great. I'm probably just doing some small mistake. Anyway, thank you for your time!
@zidek22 , I have given formulas in last update to sort the month on month order, not the text. Just adjust to your table and that will work
It worked! I added a second DateView column that I use to display the dates, then I created the DateSort column that is in a YYYYMM format and can be sorted. After that I sorted the DateView column by the DateSort column. Basicaly I never use the original Date column in my visual. Just writing this up for future reference if anyone needs it.
Thank you so much! Bye @amitchandak
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |