cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irnm8dn Member
Member

Slicer Ordering

I am looking for a suggestion how I can create a preferred order within a slicer, instead of alphabetical.

 

Any suggestions on how to do this simply and elegantly?

1 ACCEPTED SOLUTION

Accepted Solutions
pschommer Regular Visitor
Regular Visitor

Re: Slicer Ordering

I have created a simple example *.pbix file HERE. The data are from an Excel file found HERE.

 

tblSort contains the fruit in the order I want to see them (Strawberry first). As explained in the answer, we need to create a relationship between the tables. Along the left side of the PBI Desktop, select the third button (Manage Relationships). Create the relationship by dragging the SortValue field on tblSort (fruit name) to the FruitName field on tblFruit (also fruit name).

 

Now go back to the report canvas. The stacked column chart is pretty straighforward using values from tblFruit. The slicer, however, is sourced from the SortValue field on tblSort, not from the FruitName field on tblFruit. (Both would work, but only one would allow you to order the values.) Even after setting up the slicer, there is still the task of forcing a different sort order. This is done by highlighting the SortValue field in the Fields area on the far right of the desktop canvas, going to the Modeling tab, clicking Sort by Column from the ribbon, then selecting SortOrder. This is the numeric representation of the desired order of the fruit values.

 

Clicking different slicer values works just like using FruitName from tblFruit as a slicer value, but the values are in the order I want.

 

I suggest starting from a new tab and trying to recreate the scenario, or starting over with just the sample data. When that is working as expected, go back to your own dataset and give it a try. Good luck.

View solution in original post

6 REPLIES 6
Microsoft dkay84_PowerBI
Microsoft

Re: Slicer Ordering

Create a new table (in query editor) that has a column of distinct values that you are using in your slicer.

 

Add a numeric column using whatever logic you need based on the desired order.

 

Close and Apply to load this new table to data model, then create a relationship to your fact table.

 

Click on the new field of distinct values, then click on the Modeling tab and select Sort by Column.  Choose the numeric column that you created to provide the order.

 

Use this distinct list in your slicer.

pschommer Regular Visitor
Regular Visitor

Re: Slicer Ordering

Great tip!

I especially like it for even simple slicers like True/False where False is listed first alphabetically. That always bothered the obsessive in me, but I now I can remedy it with a new 2-row table.

irnm8dn Member
Member

Re: Slicer Ordering

Thanks @dkay84_PowerBI !

 

I am excited to try this solution, and understand how to add a new table.  Beyond that, can you add some detail to the steps your recommending.  I am still a Power BI newb.

 

Thanks.

pschommer Regular Visitor
Regular Visitor

Re: Slicer Ordering

I have created a simple example *.pbix file HERE. The data are from an Excel file found HERE.

 

tblSort contains the fruit in the order I want to see them (Strawberry first). As explained in the answer, we need to create a relationship between the tables. Along the left side of the PBI Desktop, select the third button (Manage Relationships). Create the relationship by dragging the SortValue field on tblSort (fruit name) to the FruitName field on tblFruit (also fruit name).

 

Now go back to the report canvas. The stacked column chart is pretty straighforward using values from tblFruit. The slicer, however, is sourced from the SortValue field on tblSort, not from the FruitName field on tblFruit. (Both would work, but only one would allow you to order the values.) Even after setting up the slicer, there is still the task of forcing a different sort order. This is done by highlighting the SortValue field in the Fields area on the far right of the desktop canvas, going to the Modeling tab, clicking Sort by Column from the ribbon, then selecting SortOrder. This is the numeric representation of the desired order of the fruit values.

 

Clicking different slicer values works just like using FruitName from tblFruit as a slicer value, but the values are in the order I want.

 

I suggest starting from a new tab and trying to recreate the scenario, or starting over with just the sample data. When that is working as expected, go back to your own dataset and give it a try. Good luck.

View solution in original post

irnm8dn Member
Member

Re: Slicer Ordering

@pschommer

 

You're a hero!  Thanks for your help.

yeszone Frequent Visitor
Frequent Visitor

Re: Slicer Ordering

Thank you sooo much.

I've done everything before seeing your reply, except the LAST point. Clicking "Sort by Order" for SortValue. I even did this for SortOrder. But until I saw your post did not realize that it can be done to all columns.

 

Smiley Very Happy

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors