Sort Visual on Field Parameter

[Dean_R]

I have just added a Field Parameter to my report to allow a visual to switch between Date, Week and Month with Week and Month as Calculated Columns in a table. It works great with the slicer changing the X-Axis based on the selection but each time it changes the visual defaults to sorting by the Y-Azis value (in this case a count).

When I set the sort order manually on the visual it recognises the selected field as the X-Axis and sorts but then as soon as the parameter is changed this is lost as you switch backwards and forwards.

I have tried creating a linked table and defining the sort order to be used in the parameter but have not had any luck.

Any ideas or is this due to the pass through of the field parameter not being able to store your sort changes when a new field has replaced the previous?

[dwlashua]

I ran in to this issue and found this helpful workaround discussed by Christine Payton using bookmarks to save the state of the default sort order: https://www.youtube.com/watch?v=Nt3QgZrJXfY

In my scenario I was using multiple paramaters which made it even more complicated (metric on the Y-axis, time period on the X-axis, and demographic dimension for the legend) so my visual would always default to the Y-axis sort order when I wanted the Time Period on the X-axis to remain fixed.

I found that if the Time Period [Year, Quarter, Month, Date] on the X-Axis is set with a Continous type (rather than a Categorical type) the dates would stay in the correct order. So instead of using the categorical labels (2023-1, 2023-2, 2023-3, etc.) I was able to use date formats (All January dates were 1/1/2023, February dates were all 2/1/2023, etc) for both months and quarter year.

DAX for month start:

DateStartOfMonth = DATE( YEAR( Snapshots[DateColumn]), MONTH(Snapshots[DateColumn]),  1)

Dax for quarter year start:

DateStartOfQuarterStart =

VAR _Quarter = QUARTER(Snapshots[DateColumn])

VAR _NewDate =

SWITCH( TRUE()

    ,_Quarter = 2, 4 //April

    ,_Quarter = 3, 7 //July

    ,_Quarter = 4, 10 //October

    ,_Quarter //January

)

RETURN

DATE(YEAR(Snapshots[DateColumn]),  _NewDate,  1)

The downside is that in the continous layout, month labels are sometimes missing:

When quarters are portrayed the labels do not line up nicely:

[Anonymous]

So, after looking into this a bit, you need to make sure the source column in the original table is sorted by another column. The tables don't need to be connected. 

https://www.youtube.com/watch?v=vo-8p7_3yZk

[M1rzA_V]

You can Sort By: Year. For me works just like that.

[lionelv]

It works very well! Fantastic!

My undestanding is that when you are shifting form a field to the other, in the meantime, PBI select another field to sort. So, selecting tooltip as the field/measure to sort, it doesn't change if you change the field.

[Anonymous]

I used another workaround - I added a blank measure to the charts tooltip and sorted by this measure. Somehow it works. 

[magiccharts]

Step by step solution:

Step 1: Create the Blank Measure

1. • Go to the "Modeling" tab in Power BI Desktop.
   • Click on "New Measure."
   • Name the measure (e.g., SortBlankMeasure) and use the following DAX formula:Create a New Measure:
   SortBlankMeasure = BLANK()

Step 2: Add the Blank Measure to the Chart

1. Select Your Visual:

   • Click on the visual (chart or table) where you want to apply the sorting.

2. Access the Fields Pane:

   • In the Visualizations pane on the right side, you'll see a section called "Fields."
   • This section shows the fields and measures currently used in the visual.

3. Add the Blank Measure to the Tooltip:

   • Scroll down to find the "Tooltip" area within the Fields section.
   • Drag the SortBlankMeasure from the Fields list and drop it into the "Tooltip" section.

   Note: The Tooltip section is usually at the bottom of the Fields pane. If you don’t see the Tooltip section, it’s possible that the visual you’re using doesn’t support tooltips, in which case this method may not be applicable.

Step 3: Sort the Visual by the Blank Measure

1. Sort the Visual:
   • Once the blank measure is added to the Tooltip, click on the three dots (ellipsis) at the top-right corner of the visual to access the "Sort by" options.
   • You should now see the SortBlankMeasure as one of the sorting options.
   • Choose to sort the visual by SortBlankMeasure.

[Winniethewinner]

This works like a magic. Thanks for sharing! 

[MVeatch]

This solved my issues...thanks for sharing!!!

[Anonymous]

Can you pls explain how to do that? Thanks in advance.

[Tummi]

Would you be able to show me an example of how you would do this? I'm having difficulty trying to figure out how to sort this field parameter.

[Anonymous]

This is the easiest workaround and it works. I tried other workarounds, but they don't work for me. Maybe because I use columns from the same table in field parameters. Thanks, Josz!

[Anonymous]

Hello, I have the same problem with field parameters - it breaks my sorting order. 
I raised it as an issue Field Parameter breaks sorting order in visuals 

[mm_4062]

Hi @lbendlin and @Dean_R , just wondering if there is a solution for this?  I am having the same issue of using the new field parameter to create date periods for the X-Axis: Month, Quarter, Year etc.  It automatically sorts alphabetically (this can be changed ascending or descending but it's not useful) by whichever option I have selected.  I cannot figure out how to tell it to sort by date (date table) or even an index.  Any ideas that have worked for you?

[Dean_R]

Thanks @lbendlin I think this may be an issue/ feature - I have tried a few ways of sorting and it gets overwritten each time, this person seems to have had the same issue: https://github.com/MicrosoftDocs/powerbi-docs/issues/3827 

[lbendlin]

As I mentioned one workaround is to enforce the sorting via the "sort a column by another column" assignment.  With the aforementioned caveat that the default is wrong (in my opinion).  If it seems not to work then you need to refresh the page/reset the filters too.  There are still a few bugs to iron out with this feature.

[lbendlin]

I think the developers of the Field Parameters feature have not considered your scenario. 
One thing you can do is explicitly "sort a column by another column"  for the field parameters you included. NOTE:  This currently assumes ASCending sort which is not optimal when you expect new values in your column. The better default would be DESC.

Alternatively you can raise it as an issue/feature request.