Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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"))))
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"))))
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"))))))))
=
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"))))))))
Its now like this, the sort is being done by ascending order on the first digit
could you share your pbix-file?
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"))))))))
where in your code?
REPT(UNICHAR(8203)
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"))))))))
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"))))))))
Its worked, I rebooted everything and started afresh, many thanks again, hopefully that will be the end of it!
Many thanks!
I now have 4 different solutions to this problem which is great, much appreciated..
@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
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.
@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
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).
User | Count |
---|---|
92 | |
86 | |
66 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |