Solved: Re: 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

Europe’s largest Microsoft Fabric Community Conference

Power BI Monthly Update - June 2024

New forum boards available in Real-Time Intelligence.

How to Get Your Question Answered Quickly

European Microsoft Fabric Community Conference