Sorting on X axis on created column

[MarkianLC]

Hello,

Am in need of assistance....I have a query returning "Elapsed minutes" as Whole Number data type. I created a new column, within PowerBI, which "categorizes" the value into "buckets of time". This created column is set up as: 

Time_Slot =

IF(Mobile_Visit_Detail[Elapsed Time]< 10,"0-10",

IF(Mobile_Visit_Detail[Elapsed Time]>=10&&Mobile_Visit_Detail[Elapsed Time]<=15,"10-15",

IF(Mobile_Visit_Detail[Elapsed Time]>15&&Mobile_Visit_Detail[Elapsed Time]<=30,"15-30",

IF(Mobile_Visit_Detail[Elapsed Time]>30&&Mobile_Visit_Detail[Elapsed Time]<=60,"30-60",

IF(Mobile_Visit_Detail[Elapsed Time]>60&&Mobile_Visit_Detail[Elapsed Time]<=180,"60-180",

IF(Mobile_Visit_Detail[Elapsed Time]>180&&Mobile_Visit_Detail[Elapsed Time]<=360,"180-360",

IF(Mobile_Visit_Detail[Elapsed Time]>360&&Mobile_Visit_Detail[Elapsed Time]<=540,"360-540",

IF(Mobile_Visit_Detail[Elapsed Time]>540&&Mobile_Visit_Detail[Elapsed Time]<=720,"540-720",

"720 +"))))))))

No matter which chart visual I use, I cannot get the sorting/ordering of the X axis (using the Time_Slot column) to sort in the ascedning "number of minutes" categories......Left to right should be:

0-10

10-15

15-30

30-60

60-180

180-360

360-540

540-720

720+

The visual displays in this order:

0-10, 10-15, 15-30, 180-360, 30-60, 60-180, 720+ 

There are two categories which have no counts. (Not displaying these two is not a problem, but an annoyance for me. Even if checking "Show items with no data", they don't display).

The Time_Slot column is  Data type of text. Changing it to whole number, or any other type doesn't work b/c of the dashes and the "+" in the 720+.

What do I need to do to get this visual to display in Time_Slot ascending order with their corresponding values.

Here's what the visual looks like now:

[lbendlin]

First thing to do is create a reference table with your desired buckets and another column indicating the sort order. 

Then you can sort the bucket name by the bucket ID

Note:  You can slightly simplify your formula

Time_Slot = SWITCH(TRUE()
,Mobile_Visit_Detail[Elapsed Time]<=10,"0-10"
,Mobile_Visit_Detail[Elapsed Time]<=15,"10-15"
,Mobile_Visit_Detail[Elapsed Time]<=30,"15-30"
,Mobile_Visit_Detail[Elapsed Time]<=60,"30-60"
,Mobile_Visit_Detail[Elapsed Time]<=180,"60-180"
,Mobile_Visit_Detail[Elapsed Time]<=360,"180-360"
,Mobile_Visit_Detail[Elapsed Time]<=540,"360-540"
,Mobile_Visit_Detail[Elapsed Time]<=720,"540-720"
,"720 +")

[MarkianLC]

Thank you. However, the sorting is still not as intended.

I created the table as such:

MVTimeSlots = {

(1,"0-10", 1)

,(2,"10-15",2)

,(3,"15-30",3)

,(4,"30-60",4)

,(5,"60-180",5)

,(6,"180-360",6)

,(7,"360-540",7)

,(8,"540-720",8)

,(9,"720 +",9)

}

................................

Then, made the relationship from the "data" table to the created table.

The updated visual is still displaying incorrectly. The visual's axis field has the "TimeSlot" and "Order" columns from the created table MVTimeSlots.

Not able to sort the Axis by "Order".

Am using PowerBI Report Server version Version: 2.100.1381.0 64-bit (January 2022).

Here's the visual:

[lbendlin]

You now need to put the text from the reference table into the X axis, not from the data table.

see attached for a simplified version.

[MarkianLC]

The reference table is "MVTimeSlots" with column "TimeSlot". It is on the Axis.

The Values is from the data table: "Count of Time_Slot". 

[lbendlin]

check that your visual is actually sorted by the X axis field.

[MarkianLC]

It looks to be sorted by the X axis.

[lbendlin · Accepted solution]

Did you set the TimeSlot column to be sorted by the ID column?

[MarkianLC]

WOW...that was it!

Thank you @lbendlin !