Sort by another column no longer work

[PowerBI_Yonghu]

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.

[Anonymous]

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?

[PowerBI_Yonghu]

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?

---

[v-yuta-msft]

@PowerBI_Yonghu ,

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.

[PowerBI_Yonghu]

The error occours in the data modeling tab sort by Column tab. I need to create a bar chart where monthName is sorted in order therefore I had to do it in the data modling tab (where error occurs).

Its somehow related to how data was loaded.  See my data below. Previously, I added a filtered rows in QueryEditor to limit the data to the latest ReleaseDate. Somehow it did not work this time, even I can not see any 2019Qtr1 rows but PowerBI still think its there. I fixed it by delete and reload the data. 

Thanks again for all the help.

Release Date	YYYYMM	MonthName	Seq
2019Qtr1	201810	Oct	1
2019Qtr1	201811	Nov	2
2019Qtr1	201812	Dec	3
2019Qtr1	201901	Jan	4
2019Qtr1	201902	Feb	5
2019Qtr1	201903	Mar	6
2019Qtr2	201901	Jan	1
2019Qtr2	201902	Feb	2
2019Qtr2	201903	Mar	3
2019Qtr2	201904	Apr	4
2019Qtr2	201905	May	5
2019Qtr2	201906	Jun	6