Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • magiccharts's avatar
    magiccharts
    2 years ago

    Step by step solution:

    Step 1: Create the Blank Measure

      • 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.
dwlashua's avatar
dwlashua
Frequent Visitor
2 years ago

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: