How to solve sort order error: 'We cannot sort the 'A' column by 'B'. You cant have more than ...'

Have you got this error while trying to sort columns?:

"We cannot sort the 'school' column by 'grade'. You can't have more than one value in 'grade' for the same value in 'school'. Choose a different column for sorting or update the data in 'Year'."

In this video I show you why you get it and how to fix it.


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[janwillemvanholst]

Thank for the video. As always much appreciated. I think a calculated column in Dax
SortSchool =
RANKX(
'Table',
CALCULATE(MIN('Table'[Grade]), ALLEXCEPT('Table', 'Table'[school])), , ASC, Dense
)

will do the trick more robust (if a data element is add, for example School=Preschool, grade = 0) and mor efficient (what if there are 100 distinct values in stead of 3).

[mansivyas]

Yo saved me Ruth. I am not the one who comment on the videos but I spend 3 days to figure this out and you made it so simple. Thank you.

[Craftydoki]

oh my god, i have been researching this for days...your tutorial is my savior. Thanks much

[tomduffhues]

Yes! I've struggled a lot with this problem getting the sorting I want, but was not able to.. Thanks!

[andresantos]

This video has saved my life a couple of times, thank you very much!

[montserratmora]

Thanks for your explanation, you saved me!

[kartickkushwaha]

Great video. Thank you so much. I spent one week on this.

[城堡观察者]

Many thanks, Ruth, I have been thinking about this issue for a long time. I used an extra table with the unique value of "Grade", thank you for your idea, now I only need to add one more order column. I think it would be better to use ""From example" if there are many rows in the table.

[dannyhometown]

Thanks Ruth, I guess I may also use Add column from examples when there has more different values. Have a good day! ;-) - Danny

[sameerulhaq]

but what if I have a large amount of data?

[omar-sidani]

If i have from 1 till 90
I should add every number for its name value?!

[PS]

It's again the error as the new column also having duplicates

[hokki]

Can you do it with DAX? You need that when tables have also been created with DAX. I get a circular error in that case

[joseagundis]

Tengo una pregunta, ¿Qué función DAX debo usar para iterar cada fila de una columna? Mi objetivo es saber si tal fila es igual a la fila siguiente de dicha columna. No sé si me expliqué bien, desde luego primero trato de investigar por mi cuenta pero no he podido obtener mi resultado. Perdón por el atrevimiento. Saludos y estoy al pendiente de ti Ruth.

[EricaDyson]

Would this work for legends of charts, where the sort order should be alphabetical but isn't? Up to now, I have created a separate measure for each item and then added them in the correct order. That works but it's a pain. So your solution would be far quicker and easier wouldn't it?

[kishanbhise]

What if i have 1000 different schools ?

[FrazierJustin]

How can you do it if there are hundreds/thousands of categories to sort? Elem, Middle, and High are only 3 categories so this is a simple fix, but what if there is a ton of data, its not as efficient