Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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) }
)
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
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) }
)
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
89 | |
80 | |
60 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |