The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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).
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
103 | |
101 | |
74 | |
59 |
User | Count |
---|---|
261 | |
123 | |
114 | |
97 | |
84 |