Forum Discussion
Number ordering issue (1,11,2,AB)
Hi all,
I have issue with my number ordering. I have the following data (and the table will have more rows):
| Day | Name | Code |
| 1 | 1-ABC | 1 |
| 1 | 2-ABC | 1 |
| 1 | 3-ABC | 10 |
| 1 | 4-ABC | 11 |
| 1 | 38-ABC | 13 |
| 2 | 50-ABC | 13 |
| 2 | 5-ABC | 2 |
| 2 | 15-ABC | AR |
| 3 | 19-ABC | 15C |
| 4 | 35-ABC | 20 |
| 5 | 44-ABC | R |
| 6 | 45-ABC | 3 |
I would like to order my table by Code ASC order, but I received reordered numbers 1,10,11,2,3 and I would like to see 1,2,3,10,11,13,ABC
| Desired result |
| 1 |
| 1 |
| 2 |
| 3 |
| 10 |
| 11 |
| 13 |
| 13 |
| 20 |
| AR |
| 15C |
| R |
I tried to add index, but it didn't work, because the code field is in text format and it contains numbers, only text,and text& number.
Do you know how can I order the Code column in the right ASC order and to add the fields with letters/letters&numbers after the numbers?
Thank you.
Hi all,
I just found the solution in another post. I used the following dax formula and it worked:Sort_Order = IF ( NOT ( ISERROR ( 'Table'[Column] + 0 ) ) = TRUE (); CONCATENATE ( REPT ( 0; 4 - LEN ( 'Table'[Column] ) ); 'Table'[Column] ); 'Table'[Column] )You can refer to the following post: https://community.powerbi.com/t5/Desktop/Number-sorting-in-text-format/m-p/237256
Thank you for the help.
3 Replies
- Pragati11
Super User
HI Anonymous ,
Can you share a screenshot on what you see when you order the CODE column in ascending order? (just this column)
What does DAY column has in your data? Is it numeric? Can it be used to create a sort order for CODE column in someway?
Thanks,
Pragati
Hi Pragati11,
This is a sample/example from my data:Code 1 1 10 11 13 13 2 AR 15C 20 R 3
The day field comes from another table. Also I have slicers for year,month.
10x.
Hi all,
I just found the solution in another post. I used the following dax formula and it worked:Sort_Order = IF ( NOT ( ISERROR ( 'Table'[Column] + 0 ) ) = TRUE (); CONCATENATE ( REPT ( 0; 4 - LEN ( 'Table'[Column] ) ); 'Table'[Column] ); 'Table'[Column] )You can refer to the following post: https://community.powerbi.com/t5/Desktop/Number-sorting-in-text-format/m-p/237256
Thank you for the help.
