Forum Discussion
How can I sort the text values in a filter object?
???
Hi Anonymous,
as meysun said you have to use "Sort By Column".
1) You have to create two calculated columns ("monthName" and "monthNumber") and visualize monthName as a filter.
2) After that you have to select "Sort By Column" and sort by monthNumber, and you gain the result.
Let Me Know If It Works.
#I'M Not An Expert#
18 Replies
The solution is far easier than I thought.
Steps
- In the "Fields" pane, click on the field that is being dispalyed.
- On the "Modeling" tab, click "Sort By Column".
- Chose the column that you want to sort the displayed field by.
- Viola
Based on your description, you would like to sort text values in the Slicer control. You can click the (...) part to to select Sort By to sort the slicer alphabetically by text values in the slicer.
Hi Anonymous,
as meysun said you have to use "Sort By Column".
1) You have to create two calculated columns ("monthName" and "monthNumber") and visualize monthName as a filter.
2) After that you have to select "Sort By Column" and sort by monthNumber, and you gain the result.
Let Me Know If It Works.
#I'M Not An Expert#
I see that you are trying to sort months chronolically.
If the user entered the months, Power BI will consider them as text and will not convert them to dates even if you tried to in the formatting section, it won't let you.
I found a 'not very nice' workaround to that...
Consider that the months you want to sort are in a column named 'Text'. Add a new column: Month = Table1[Text]&"-16"
This new column will automatically take the data type date.Add the new column 'Month' to an empty table, you will have the following values in the visualizations pane
Remove year, quarter, and day. Only the month will stay and it will be considered as a date, that way you can sort it according to time and not alphabetically.
Then you can transform your table into a slicer and months will stay ordered.
I tried to extract the month directly from the new column but that didn't work.
If someone has a better and neater solution, please let us know.
Best!You can use the 'Sort by column' approach https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/
The solution is far easier than I thought.
Steps
- In the "Fields" pane, click on the field that is being dispalyed.
- On the "Modeling" tab, click "Sort By Column".
- Chose the column that you want to sort the displayed field by.
- Viola
Thank you ,
I have quick question. sorting is working fine in Power BI Desktop version. when i publish to power BI services, it's not shown sorting order
Any idea?
I created a calculated column which concatenates month number and month name, and then use that column as a filter:
ordered mont = format('table name'[month number];"00") & ". " & 'table name'[month name]
The Format functiion adds a zero before months 1, 2 and 3, as this column is now a text column.
Gabriel.
Hi,
I've made the same comment on a different similar topic:
In my case I have color states: red, amber, yellow, green. I've created a calculated column with an IF function; IF color sates = "Red" then "A", then "B", then "C"... Now I click on color state/Modelling/Sort by column and click on my new calculated column. But it gives back the following message: "This column cant't be sorted by a column that is already sorted, directly or indirectly, by this column."
Any tip?
Thanks in advance!
