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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Dean_R
New Member

Sort Visual on Field Parameter

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.

image.png

image.png

image.png

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?

15 REPLIES 15
dwlashua
Frequent Visitor

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 = DATEYEARSnapshots[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:

dwlashua_1-1706792116778.png

 

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

dwlashua_0-1706792042238.png

 

 

 

naledi_h
Helper I
Helper I

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
Frequent Visitor

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

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
Not applicable

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

This works like a magic. Thanks for sharing! 

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

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

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.

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
Not applicable

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
Frequent Visitor

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
New Member

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 

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
Super User
Super User

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors