Forum Discussion
Power ParticipantUnable to sort column in correct order
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
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"))))
15 Replies
- amitchandak
Super User
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
ArchStantonThanks, 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).
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.
- Ahmedx
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
Many thanks!
I now have 4 different solutions to this problem which is great, much appreciated..
- 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"))))))))
