Forum Discussion

sassy0112's avatar
sassy0112
Frequent Visitor
1 year ago
Solved

Sorting by Column not displayed when using a Field Parameter

Hi,

 

I have a line graph that uses a field parameter to switch the x-axis between date, month-year, and week-year. I want to sort on a differnt column than I'm displaying in the graph. I know it is possible to do this using a sort column, but is it possible to use a sort column in combination with a field parameter? 

In my parameter I'm using a FORMAT function to get the month and week looking as I want to actually display it in the graph. This all works fine except it will not sort chronologically. I've tried everything I can think of to get this to work. I'd really like to avoid creating a separate chart for each date level and toggling between them. 

 

Here is my set up:

dimDate - Table containing the date info

  •    I added a column to format for displaying in the chart: MonthFormatted = FORMAT(dimDate[Date], "mmm yyyy")
  •    I added a column to create a numeric sort column used only for sorting: MonthSort =  FORMAT(dimDate[Date], "myyyy")

 

My field parameter code looks like this:

Parameterized Date = {
    ("Day", NAMEOF('dimDate'[Date]), 0),
    ("Month", NAMEOF('dimDate'[MonthFormatted]), 2),
    ("Week Of Year", NAMEOF('dimDate'[WeekFormatted]), 1)
}

 

I've tried the following things none of which worked:

  • Sorting dimDate by the MonthSort column in the sort option of column tools
  • Adding MonthSort to the tooltip - this doesn't work because it wants to aggregate
  • Creating a blank measure and adding it to the tooltip
  • Sorting the field parameter using column tools

What am I missing? No matter what I try, it either sorts it alphabetically or not at all.

Thanks in advance for your help.

Need Help
  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi sassy0112 

    Sort Column in the Data Model (Both the columns used in the Field Parameter)

    Ensure your columns (e.g., Month-Year, Week-Year) have corresponding numeric sort columns (like MonthNumber or WeekNumber) defined in your data model.

    Use Power BI’s Sort by Column feature to link the display column to the numeric sort column.

    This should follow even when switiching the fields

4 Replies

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Super User
    1 year ago

    Hi sassy0112 

    Sort Column in the Data Model (Both the columns used in the Field Parameter)

    Ensure your columns (e.g., Month-Year, Week-Year) have corresponding numeric sort columns (like MonthNumber or WeekNumber) defined in your data model.

    Use Power BI’s Sort by Column feature to link the display column to the numeric sort column.

    This should follow even when switiching the fields

    • sassy0112's avatar
      sassy0112
      Frequent Visitor
      1 year ago

      Hi, Thank you for the response. This doesn't work for me for some reason. I have a sort key in the dimDate table, which are YYYYMM and YYYYWW. When I do a column sort on the table it ignores it. I'm not sure if this is because I'm using a field parameter in the chart.

    • sassy0112's avatar
      sassy0112
      Frequent Visitor
      1 year ago

      I realized what I was doing wrong, and your solution worked! In column sort I thought you simply picked the column to sort by, when in fact you are selecting the display column, then selecting the column to sort that column by. Once I realized that it works like a charm.