Slicer Custom Sort

[newpbiuser01]

Hello,

I need to create a custom sort based for a slicer where the values are going to be all the fruits in the dataset and also an "All Fruits" option. I need to sort the column so the "All Fruits" is always on the top and the remaining values in the column are sorted automatically in an alphabetical order. I tried creating a conditional column to assign a sort ID but that wouldn't account for any new fruits being added in the data set. So for example if "Peach" was added to the dataset in the next month, it would automatically be assigned the SortID 3 and the following fruits would move down in the list. 

Cell Value	SortID
All Fruits	1
Apple	2
Pear	3
Orange	4

Thank you!

[v-easonf-msft]

Hi, @newpbiuser01 

'SortID' column is recalculated and created every time the data is refreshed.

If you want to automatically assign a Sort ID based on the time the record was added, then you have to consider adding a datetime column in your original datasoure as the basis for adding the SortID.

Best Regards,
Community Support Team _ Eason

[johnt75 · Accepted solution]

You could create a table which will add new values when they appear as

New Table = 
var fruits = VALUES('Table'[Fruit])
return UNION( 
ADDCOLUMNS( fruits, "Sort order", RANKX(fruits, [Fruit], , ASC),
{ ( "All fruits", -1) }
)