Unable to sort column in correct order

[ArchStanton]

Hi,

I have a problem that I thought I fixed a while ago but is still there.

I have inherited a PowerBI report and one of the datamodel tables has a column that groups the age of the 'x' into various age categories = 

Age Profile +12 (Validation Months) = 
if( 'Cases'[Case Length (Validation Date)] < 365 , " 0-12 Months",
    if('Cases'[Case Length (Validation Date)] < 455, " 12-15 Months",
        if('Cases'[Case Length (Validation Date)] < 545, " 15-18 Months", 
            if('Cases'[Case Length (Validation Date)] < 635, " 18-21 Months", 
            "21+ Months"))))

This has been sorted so that each age Category appears in Ascending Order in my Table visual = 

However, I have been asked to expand these Age Categories upto 24 months + and so I have created another Calculated Column to do so = 

Age Profile2 = 
if( 'Cases'[Case Length (Validation Date)] < 90.25 , "0-3 Mths",
    if('Cases'[Case Length (Validation Date)] < 182.5, "3-6 Mths",
        if('Cases'[Case Length (Validation Date)] < 273.75, "6-9 Mths", 
            if('Cases'[Case Length (Validation Date)] < 365.25, "9-12 Mths", 
                if('Cases'[Case Length (Validation Date)] < 456.25, "12-15 Mths",
                   if('Cases'[Case Length (Validation Date)] < 547.5, "15-18 Mths", 
                      if('Cases'[Case Length (Validation Date)] < 638.75, "18-21 Mths", 
                         if('Cases'[Case Length (Validation Date)] < 730, "21-24 Mths",
                         "24+ Mths"))))))))

Why can't I sort this column is Ascending order?

I have tried creating a Rank column to sort it on but that still doesn't work = 

Rank Column Code =

RANK2 Sort = SWITCH(
            TRUE(),
                'Cases'[Age Profile2] = "0-3 Mths",1,
                'Cases'[Age Profile2] = "3-6 Mths",2,
                'Cases'[Age Profile2] = "6-9 Mths",3,
                'Cases'[Age Profile2] = "9-12 Mths",4,
                'Cases'[Age Profile2] = "12-15 Mths",5,
                'Cases'[Age Profile2] = "15-18 Mths",6,
                'Cases'[Age Profile2] = "18-21 Mths",7,
                'Cases'[Age Profile2] = "21-24 Mths",8,
                9)

Can anyone please help as this issue was originally opened a few months ago and everything I try fails.

Any help would be greatly appreciated!

Thanks

[Ahmedx · Accepted solution]

Age Profile +12 (Validation Months) = 
if( 'Cases'[Case Length (Validation Date)] < 365 , REPT(UNICHAR(8203),5)&" 0-12 Months",
    if('Cases'[Case Length (Validation Date)] < 455, REPT(UNICHAR(8203),4)&" 12-15 Months",
        if('Cases'[Case Length (Validation Date)] < 545, REPT(UNICHAR(8203),3)&" 15-18 Months", 
            if('Cases'[Case Length (Validation Date)] < 635, REPT(UNICHAR(8203),2)&" 18-21 Months", 
            REPT(UNICHAR(8203),1)&"21+ Months"))))

[ArchStanton]

Hi,

I thought the issue was fixed but it hasn't. I've used your code above but modified it, I still cannot sort from 0-3 to 24mths

Modified Code: 

Age Profile4 = 
if( 'Cases'[Case Length (Adj)] < 90, REPT(UNICHAR(8203),9)&" 0-3 Months",
    if('Cases'[Case Length (Adj)] < 182.5, REPT(UNICHAR(8203),8)&" 3-6 Months",
        if('Cases'[Case Length (Adj)] < 273.5, REPT(UNICHAR(8203),7)&" 6-9 Months",
            if('Cases'[Case Length (Adj)] < 365.25, REPT(UNICHAR(8203),6)&" 9-12 Months",
                if('Cases'[Case Length (Adj)] < 456.25, REPT(UNICHAR(8203),5)&" 12-15 Months",
                    if('Cases'[Case Length (Adj)] < 547.5, REPT(UNICHAR(8203),4)&" 15-18 Months",
                        if('Cases'[Case Length (Adj)] < 638.75, REPT(UNICHAR(8203),3)&" 18-21 Months",
                            if('Cases'[Case Length (Adj)] < 730.5, REPT(UNICHAR(8203),2)&" 21-24 Months",
                                REPT(UNICHAR(8203),1)&"24+ Months"))))))))

=

[Ahmedx]

write with a space at the beginning "24+ Months"

REPT(UNICHAR(8203),1)&" 24+ Months")

Age Profile4 = 
if( 'Cases'[Case Length (Adj)] < 90, REPT(UNICHAR(8203),9)&" 0-3 Months",
    if('Cases'[Case Length (Adj)] < 182.5, REPT(UNICHAR(8203),8)&" 3-6 Months",
        if('Cases'[Case Length (Adj)] < 273.5, REPT(UNICHAR(8203),7)&" 6-9 Months",
            if('Cases'[Case Length (Adj)] < 365.25, REPT(UNICHAR(8203),6)&" 9-12 Months",
                if('Cases'[Case Length (Adj)] < 456.25, REPT(UNICHAR(8203),5)&" 12-15 Months",
                    if('Cases'[Case Length (Adj)] < 547.5, REPT(UNICHAR(8203),4)&" 15-18 Months",
                        if('Cases'[Case Length (Adj)] < 638.75, REPT(UNICHAR(8203),3)&" 18-21 Months",
                            if('Cases'[Case Length (Adj)] < 730.5, REPT(UNICHAR(8203),2)&" 21-24 Months",
                                REPT(UNICHAR(8203),1)&" 24+ Months"))))))))

[ArchStanton]

Its now like this, the sort is being done by ascending order on the first digit

[Ahmedx]

could you share your pbix-file?

[ArchStanton]

I can't as its linked to a Live Dynamics365 system with lots of sensitive data in it. 

A copy of the code for you if that helps?

Age Profile3 = 
if( 'Cases'[Case Length (Adj)] < 90.25 , " 0-3 Mths",
    if('Cases'[Case Length (Adj)] < 182.5, " 3-6 Mths",
        if('Cases'[Case Length (Adj)] < 273.75, " 6-9 Mths", 
            if('Cases'[Case Length (Adj)] < 365.25, " 9-12 Mths", 
                if('Cases'[Case Length (Adj)] < 456.25, " 12-15 Mths",
                   if('Cases'[Case Length (Adj)] < 547.5, " 15-18 Mths", 
                      if('Cases'[Case Length (Adj)] < 638.75, " 18-21 Mths", 
                         if('Cases'[Case Length (Adj)] < 730, " 21-24 Mths",
                         " 24+ Mths"))))))))

[Ahmedx]

where in your code?

REPT(UNICHAR(8203)

[ArchStanton]

Sorry, here it is:

 this has the space before the 24mths

if( 'Cases'[Case Length (Adj)] < 90, REPT(UNICHAR(8203),9)&" 0-3 Mths",
    if('Cases'[Case Length (Adj)] < 182.5, REPT(UNICHAR(8203),8)&" 3-6 Mths",
        if('Cases'[Case Length (Adj)] < 273.5, REPT(UNICHAR(8203),7)&" 6-9 Mths",
            if('Cases'[Case Length (Adj)] < 365.25, REPT(UNICHAR(8203),6)&" 9-12 Mths",
                if('Cases'[Case Length (Adj)] < 456.25, REPT(UNICHAR(8203),5)&" 12-15 Mths",
                    if('Cases'[Case Length (Adj)] < 547.5, REPT(UNICHAR(8203),4)&" 15-18 Mths",
                        if('Cases'[Case Length (Adj)] < 638.75, REPT(UNICHAR(8203),3)&" 18-21 Mths",
                            if('Cases'[Case Length (Adj)] < 730.5, REPT(UNICHAR(8203),2)&" 21-24 Mths",
                                REPT(UNICHAR(8203),1)&" 24+ Mths"))))))))

[Ahmedx]

you do not understand, I tell you you must write a space at the beginning " 24+ Mths"

Возрастной профиль4 = 
 if( 'Делания'[Длина дела (Adj)] < 90, REPT(UNICHAR(8203),9)&" 0-3 Mths",
     if('Делания'[Длина дела (Adj)] < 182,5, REPT(UNICHAR(8203),8)&" 3-6 Mths",
         if('Делания'[Длина дела (Adj)] < 273,5, REPT(UNICHAR(8203),7)&" 6-9 Mths",
             if('Делания'[Длина дела (Adj)] < 365,25, REPT(UNICHAR(8203),6)&" 9-12 Mths",
                 if('Делания'[Длина дела (Adj)] < 456,25, REPT(UNICHAR(8203),5)&" 12-15 Mths",
                     if('Делания'[Длина дела (Adj)] < 547,5, REPT(UNICHAR(8203),4)&" 15-18 Mths",
                         if('Делания'[Длина дела (Adj)] < 638,75, REPT(UNICHAR(8203),3)&" 18-21 Mths",
                             if('Делания'[Длина дела (Adj)] < 730,5, REPT(UNICHAR(8203),2)&" 21-24 Mths",
                                 REPT(UNICHAR(8203),1)&" 24+ Mths")))))))) 

[ArchStanton]

Its worked, I rebooted everything and started afresh, many thanks again, hopefully that will be the end of it!

[ArchStanton]

Many thanks!

I now have 4 different solutions to this problem which is great, much appreciated..

[Anonymous]

@ArchStanton Create one more calculated column
Age Profile 3 =
if( 'Cases'[Case Length (Validation Date)] < 90.25 , 1,
if('Cases'[Case Length (Validation Date)] < 182.5, 2,
if('Cases'[Case Length (Validation Date)] < 273.75, 3,
if('Cases'[Case Length (Validation Date)] < 365.25,4,
if('Cases'[Case Length (Validation Date)] < 456.25,5,
if('Cases'[Case Length (Validation Date)] < 547.5,6,
if('Cases'[Case Length (Validation Date)] < 638.75, 7,
if('Cases'[Case Length (Validation Date)] < 730, 8,
9))))))))
Once this column is created go to Age profile 2 and then Sort by column Age Profile 3 and your sorting will work

[ArchStanton]

Thanks. I actually managed to fix it by creating a new Table in Query Editor showing just 2 columns = Rank No and Age Profile which I then sorted on Rank 1 to 8.

I then created a join on this table using 'Rank' to my Main Data Table and then used the Age Profile from my new table and it worked.

[amitchandak]

@ArchStanton , One of the two. I usually add additional space in name to make sure they sort, may be start with 5-6 space and keep on reducing

Create from same base column

Age Profile2 Sort=
if( 'Cases'[Case Length (Validation Date)] < 90.25 , 0,
if('Cases'[Case Length (Validation Date)] < 182.5, 1,
if('Cases'[Case Length (Validation Date)] < 273.75,2,
if('Cases'[Case Length (Validation Date)] < 365.25, 3,
if('Cases'[Case Length (Validation Date)] < 456.25, 4,
if('Cases'[Case Length (Validation Date)] < 547.5, 5,
if('Cases'[Case Length (Validation Date)] < 638.75, 6,
if('Cases'[Case Length (Validation Date)] < 730, 7,
8))))))))

prefer Switch

Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

[ArchStanton]

Thanks, I'll look at this option again if the need arises as I've found a different solution which so far is working (see my reply to Surya9 above).