topic Re: Sorting on X axis on created column in Report Server

Sorting on X axis on created column

MarkianLC — Fri, 16 Sep 2022 17:55:56 GMT

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:

Re: Sorting on X axis on created column

lbendlin — Sat, 17 Sep 2022 18:05:14 GMT

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 +")

Re: Sorting on X axis on created column

MarkianLC — Mon, 19 Sep 2022 14:00:10 GMT

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:

Re: Sorting on X axis on created column

lbendlin — Mon, 19 Sep 2022 14:38:34 GMT

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.

Re: Sorting on X axis on created column

MarkianLC — Mon, 19 Sep 2022 14:48:53 GMT

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

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

Re: Sorting on X axis on created column

lbendlin — Mon, 19 Sep 2022 15:28:51 GMT

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

Re: Sorting on X axis on created column

MarkianLC — Mon, 19 Sep 2022 17:19:25 GMT

It looks to be sorted by the X axis.

Re: Sorting on X axis on created column

lbendlin — Mon, 19 Sep 2022 17:24:11 GMT

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

Re: Sorting on X axis on created column

MarkianLC — Mon, 19 Sep 2022 17:31:49 GMT

WOW...that was it!

Thank you lbendlin !