sort by custom Index order

[kfschaefer]

Still looking for assistance in establising a correct sort order for Slicer:

I create a custom column to set the new Index order, however, when I try to implement it as a slicer it reverts back the alpha sort instead on the established sort order.

the table view looks correct,however, when I change to slicer it reverts back.

NOTE:  THE 3000+ is out of order, it should follow the 501-3000 selection option.

Please see my previous post:

http://community.powerbi.com/t5/Desktop/Reset-INDEX-order-when-data-sort-does-not-give-the-results/m...

[GilesWalker]

@kfschaefer I beleive the issue you are having is to do with telling PBI how to sort. In PBI desktop click on the data button (looks like a table):

Then click the modelling tab in the ribbon. In the picture above you can see a button called Sort By Column. If you select table 'LiveRangeList' and then select column [Live Range] the Sort by column button will then allow you to sort the Live Range column by the SortID. This will maintain the desired sorting you require.

If this doesnt make sense please let me know.

Thanks,

Giles

[kfschaefer]

I already did that - the issue is when I change the table visualization to a slicer the sort order does not hold.

K

[Sean]

@kfschaefer 

If you don't mind the numbers in the slicer try concatenating the 2 columns. This will guarantee they'll be sorted the way you want!

 

[kfschaefer]

Thank you for the suggestion, however my SortID is a calculated field and cannot be using in the suggested method:

= Table.AddColumn(#"Filtered Rows", "SortID", each if [Lives Range]="0-100" then 1 else(
  if [Lives Range]="101-500" then 2 else(
  if [Lives Range]="501-3000" then 3 else(
  if [Lives Range]="3000+" then 4 else(
  if [Lives Range]="New" then 5 else(
  if [Lives Range]="Runout" then 6 else 999))))))

I am also having the same issue with the Months Slicer.  When I convert the table to slicer I loose the correct sort order.

[Sean · Accepted solution]

@kfschaefer I can see the SortID was created in the Query Editor - that will not affect this!

What I suggested is to concatenate the 2 Columns in the Data View and then use that new concatenated column for your slicer

the concatenated column is a new column with a new name - thats why it has ! at the end

in the Data View => Modeling Tab => click New Column => type Live Range! = .....

[kfschaefer]

thanks I tried it again and this time it worked.

Thanks for your input.

[Sean]

You can do the same for your Month Slicer.

If your Calendar Table has Month Number and Month Name Columns

Create a new Column by Concatenating the 2 in the Data View

So you'll get => 01 - January ..... 12 - December which you can then use as your sorted slicer