Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vitexo87
Post Prodigy
Post Prodigy

How can I sort the text values in a filter object?

Untitled.png???

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @vitexo87,

 

   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.

 

sortFilterMonths (1).png

 

2) After that you have to select "Sort By Column" and sort by monthNumber, and you gain the result.

 

sortFilterMonths (2).png

Let Me Know If It Works.

 

#I'M Not An Expert#

View solution in original post

18 REPLIES 18
sailtango
New Member

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!

Tcook
Advocate II
Advocate II

The solution is far easier than I thought. 

 

Steps

  1. In the "Fields" pane, click on the field that is being dispalyed.
  2. On the "Modeling" tab, click "Sort By Column".
  3. Chose the column that you want to sort the displayed field by.
  4. 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?

Tcook
Advocate II
Advocate II

The solution is far easier than I thought. 

 

Steps

  1. In the "Fields" pane, click on the field that is being dispalyed.
  2. On the "Modeling" tab, click "Sort By Column".
  3. Chose the column that you want to sort the displayed field by.
  4. Viola
SabineOussi
Skilled Sharer
Skilled Sharer

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
Values.PNG

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!

Vicky_Song
Impactful Individual
Impactful Individual

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.

 

Slicer Sort.png

Sorry did not ask the right way, I need to create a classification of months in the filter object, eg January, February, March ..... and now I can only sort in alphabetical order

Anonymous
Not applicable

Hi @vitexo87,

 

   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.

 

sortFilterMonths (1).png

 

2) After that you have to select "Sort By Column" and sort by monthNumber, and you gain the result.

 

sortFilterMonths (2).png

Let Me Know If It Works.

 

#I'M Not An Expert#

Anonymous
Not applicable

Thanks its worked for me.

 

Regards,

Srinivas.

I have a chronologically ordered data record, already sort the original database, I have data for the period August-December 2017 and January 2018, when inserting a graph of columns the months are displayed in a disorderly manner, the data for January are shown 2018 in the middle of the graph, when they should appear at the end. I appreciate your helpREPORTE PROBLEMA.PNGREPORTE PROBLEMA 2.PNG

 

I have a chronologically ordered data record, and I already ordered the original database, I have data for the August-December 2017 and January 2018 periods

REPORTE PROBLEMA.PNGREPORTE PROBLEMA 2.PNG

Bue dia solicito su ayuda en lo siguiente 

Necesito mostrar un grafico de columnas de forma ordeanada, tengo datos agosto- diciembre 2017 y enero 2018, al graficar, no se organiza la infmoación de forma cronologica. Ya ordene los datos en la data pero en el reporte no quedan organizados 

 

Hi, I did that but didn't work for me 😞 

 

I have a weekdays slicer and tried the same..I created a column of week days numbers and sorted by it ....it did sort the table but nothing changed on the report page the slicer is sorted alphabetically 

 Help Please !!!!!

Thanks =D works well!!!

@Anonymous is absolutely right.

However this only works when the initial column to sort is already of date type. Mine works when the months names are hardcoded and do not come from a date field.

 

Unfortunately, there is a lot of improvement that has to be made in data types and converting.

Anonymous
Not applicable

@SabineOussi I just saw your solution and I think that it's really great, I've never thought about that.

It can be very helpful!!

meysun
Resolver IV
Resolver IV

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.