Forum Discussion
Sort by another column no longer work
I have build an report which used a dummy numeric number (Seq) from 1-12 to sort my month name columns (Jan-Dec) which worked perfectly.
But now I am getting an error saying "We can't sort the monthname column by numeric variable because more than one value for the same value in monthname". I double validated the data and its 1 to 1 meaning for each month name there is only one numberic number.
I have saved pre-existed report which worked where the sort by worked perfectly. The error occurs after I change the sortby to default and trying to remap it to the numeric sort by value (Seq).
| Reporting_Period | EnrMonth | MonthName | Seq |
| Prior Period | 201704 | April | 1 |
| Prior Period | 201705 | May | 2 |
| Prior Period | 201706 | June | 3 |
| Prior Period | 201707 | July | 4 |
| Prior Period | 201708 | August | 5 |
| Prior Period | 201709 | September | 6 |
| Prior Period | 201710 | October | 7 |
| Prior Period | 201711 | November | 8 |
| Prior Period | 201712 | December | 9 |
| Prior Period | 201801 | January | 10 |
| Prior Period | 201802 | February | 11 |
| Prior Period | 201803 | March | 12 |
| Current Period | 201804 | April | 1 |
| Current Period | 201805 | May | 2 |
| Current Period | 201806 | June | 3 |
| Current Period | 201807 | July | 4 |
| Current Period | 201808 | August | 5 |
| Current Period | 201809 | September | 6 |
| Current Period | 201810 | October | 7 |
| Current Period | 201811 | November | 8 |
| Current Period | 201812 | December | 9 |
| Current Period | 201901 | January | 10 |
| Current Period | 201902 | February | 11 |
| Current Period | 201903 | March | 12 |
Thanks in advance for the help.
4 Replies
Your Seq column has repeating values, so DAX doesnt know how to sort that data when there are two fields with, say, 1. Need to figure out some other logic so that there are not repeating figures. I dont know enough about your data to suggest anything though, but couldnt you just sort by your EnrMonth column?
Thanks for the suggestion. It worked well before which tells me it is able to reconize two rows with the same data.
No, sort by enrMonth is not goingto work here becuase it's 1 to 2 match (One month name will get two EnrMonth) I will get the same error msg which now makes perfect sense.
Anonymous wrote:Your Seq column has repeating values, so DAX doesnt know how to sort that data when there are two fields with, say, 1. Need to figure out some other logic so that there are not repeating figures. I dont know enough about your data to suggest anything though, but couldnt you just sort by your EnrMonth column?
Do you mean this error happens when you sort by [sep] in a visual like below?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
