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

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

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
dkay84_PowerBI
Microsoft
Microsoft

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.

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.

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

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

@pschommer

 

You're a hero!  Thanks for your help.

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.