Solved: Unable to sort column in correct order

ArchStanton · ‎01-04-2023

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 · ‎01-04-2023

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"))))

View solution in original post

Ahmedx · ‎01-04-2023

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 · ‎01-11-2023

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 · ‎01-11-2023

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 · ‎01-11-2023

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

Ahmedx · ‎01-11-2023

could you share your pbix-file?

ArchStanton · ‎01-11-2023

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 · ‎01-11-2023

where in your code?

REPT(UNICHAR(8203)

ArchStanton · ‎01-11-2023

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 · ‎01-11-2023

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 · ‎01-11-2023

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

ArchStanton · ‎01-04-2023

Many thanks!

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

Surya9 · ‎01-04-2023

@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 · ‎01-04-2023

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 · ‎01-04-2023

@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

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

ArchStanton · ‎01-04-2023

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).

Unable to sort column in correct order

Helpful resources

Power BI Monthly Update - April 2024

How to Get Your Question Answered Quickly