Sorting by Column not displayed when using a Field Parameter

[sassy0112]

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.

[Ritaf1983]

Hi @sassy0112 
Please refer to my solution in the linked post:
https://community.fabric.microsoft.com/t5/Desktop/Sorting-custom-Period-calculated-table/m-p/4792741...

It includes PBIX with the example

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[MohamedFowzan1 · Accepted solution]

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]

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.

[sassy0112]

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.