Forum Discussion

MarkianLC's avatar
MarkianLC
New Member
3 years ago
Solved

Sorting on X axis on created column

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:
 

 

 
Ordering
sorting
X axis
  • lbendlin's avatar
    lbendlin
    3 years ago

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

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
      MarkianLC
      New Member
      3 years ago

      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's avatar
        lbendlin
        Super User
        3 years ago

        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.