Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sanoraya
Frequent Visitor

Sorting the Slicer

Hi, 

I am struggling with how to sort the slilcer the way I need: 

 

I have a table like this: 

Number Month

1           Jan

2           Feb. 

3           March

etc... 

 

The slicer only allows sorting alphabetically, but I want the months in the Slicere to be sorted by their Number. I know now the Slicer suprisingly doesn't allow to sort by another column in the table. So what are my options? 

I followed this recommerndation https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/ and  sorted by the Number in the QueryEditor, but it has no effect on how the Months are displayed. Am I doing somehtign wrong, or is it Slicer's specific?

 

Thanks a lot!

 

 

 

 

 

2 ACCEPTED SOLUTIONS
GilesWalker
Skilled Sharer
Skilled Sharer

@sanoraya - To order your months by a number range I go to the data tab in Power BI desktop:

 

Data tab.PNG

 

In the ribbon you will see the Sort By Column button. Select the column you are interested in first (in my picture it would be Months), then click the Sort By Columns button.

 

Now you need to have a column which lists the months in the numeric order you want. For example, January will always show up as 1. In the sort by column button you will then need to click the appropriate column containing the month numbers.

 

This will then sort your slicer the way you want.

 

Thanks,

 

Giles

View solution in original post

Anonymous
Not applicable

Hi @sanoraya,

 

   try following my post here.

Let me know if it works.

 

#I'M Not An Expert#

View solution in original post

20 REPLIES 20
Anonymous
Not applicable

The suggested solution is the ideal one. However, you might get caught by circular dependency errors too if it's a more complex measure rather than something simple like the static list of months as suggested.

 

Another workaround would be to list your options in excel (column 1), create the corresponding order in the next column and paste this as a new table into Power Bi. In the data relationships tab, create a relationship between the real field and the list of options you just created as column 1. Then use Column 1 in the slicer and order by column 2 in the same manner as the suggested solution in this thread. 

bjorba
Regular Visitor

Aren't helped of suggested solution due to I have my data on months level and then want to use a calendar table on month level instead of date level.

 

There are another Slicer called "HierarchySlicer" that solves it by having a non visible Value field that are possible to sort on. Another good future with that are that you can use multible columns in an Hierarchy of what you want to filter.  Something that should be included by the standard microsoft slicer!!

 

Anonymous
Not applicable

bjorba, you made my day, thanks

Tcook
Advocate II
Advocate II

The solution is far easier than I thought. 

 

Steps

  1. In the "Fields" pane, click on the field that is being dispalyed.
  2. On the "Modeling" tab, click "Sort By Column".
  3. Chose the column that you want to sort the displayed field by.
  4. Viola

This option no longer exists on my Modeling tab. Has it been moved?

Anonymous
Not applicable

The option is right there as usual:

In this example, I sorted my reports for the Superintendent Column, which is sorted by the Order Column so, is not get sorted alphabetically but by the numbers set in the Order Column

 

jmcl70_0-1666204857521.png

 

Ahh I see what I did wrong. I was clicking on the field within the hierarchy, instead of the calculated column itself. I found it, thanks!

Anonymous
Not applicable

Thanks Tcook !!! From all posts I've read, this is the only way it's working. And yes it is so simple...

Vola! Esta es la solucion, muchas gracias

Anonymous
Not applicable

Hi @sanoraya,

 

   try following my post here.

Let me know if it works.

 

#I'M Not An Expert#

Thank you very much!

GilesWalker
Skilled Sharer
Skilled Sharer

@sanoraya - To order your months by a number range I go to the data tab in Power BI desktop:

 

Data tab.PNG

 

In the ribbon you will see the Sort By Column button. Select the column you are interested in first (in my picture it would be Months), then click the Sort By Columns button.

 

Now you need to have a column which lists the months in the numeric order you want. For example, January will always show up as 1. In the sort by column button you will then need to click the appropriate column containing the month numbers.

 

This will then sort your slicer the way you want.

 

Thanks,

 

Giles

@GilesWalker - Thanks for the above referenced solution... I'm able to make that work for a single data field slicer, but do you (or anyone) have recommendations for a two-field slicer?   

 

I have the following underlying datatable (used for page navigation).  NOTE:  I'm using the Table (Rather than Button Bookmark/Page Navigator to make this more dynamic and because I'm using the slicer selection to display page description and other information in a separate dialog box)

DTaraboletti_0-1693925772401.png

 

And that table is used for the following slicer

DTaraboletti_1-1693925816697.pngDTaraboletti_2-1693925843791.png

As noted above, I am using Section (Long Name) and Page as the slicer options.  For this arrangement, I get the following error message when trying to "Sort by Column" with Page selected and the sort order option of "Sort Order" column.

DTaraboletti_3-1693925962329.png

 

Having received that error, I tried to do the "Sort By Column" option on "Tab Name" column.  The "Sort By Column" function works (no error) but when I adjust the Slicer as follows..

DTaraboletti_4-1693926241202.png

 

The Slicer sort sequence is not updated and remains as shown below

DTaraboletti_5-1693926300901.png

I would prefer not to have to create 2 separate slicers (one for section and a dependent one for Page)... so I'm seeking advice from this community. 

 

 

 

 

 

 

 

Ah, so you select the column that you want to show on your slicer (e.g. Month name), then you click on 'Sort By Column' and sort by the month number column.

Thank you!

I cannot see this working.

 

My report seems completely agnostic to sort order in the tables view. The control only gives me option of sorting in the order of the field itself 😞

 

EDIT: Hey Presto! I got it to work. It is crucial to understand that sort order is specified separately on each column, and not on the table as a whole. In the typical scenario, where one wants the slicer sorted according to values in another column, the relevant sort order must be specified on the column holding the display values of the slicer.

Anonymous
Not applicable

Thank you!!  I wasn't getting it to work until I saw your edit!  

Nice one @Dag. That's the key 🙂 

Is there a way to sort in descending order? I have 3 columns and ID, Name, and Count. I want to sort by the largest count first.

Hi, I did that but didn't work for me 😞 

 

I have a weekdays slicer and tried the same..I created a column of week days numbers and sorted by it ....it did sort the table but nothing changed on the report page the slicer is sorted alphabetically 

 Help Please !!!!!

Werafa
Regular Visitor

Me too.

 

want calendar sorted by fin year.

Slicer is randomly sorted, and does not respond to sorting the source data table

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.