Need help sorting X-axis

[rmcgrath]

Trying to sort my X-axis where my values are text.  I needed to show "Week-Day" and so I end up with values like "103-1", "98-2", etc.  The problem I'm having after clicking the three dot menu on the line chart and sorting by "Week-Day", "Ascending" is any of the 100 numbers come right after zero?  I assume because they start with 1?  How could I fix this so they are in the proper order?

[SamsonTruong · Accepted solution]

Hi @rmcgrath ,

One solution to this is creating a new calculated column for sorting. The DAX calculated column would be as follows:

WeekDaySortOrder = 
VAR WeekNum = VALUE(LEFT('YourTable'[WeekDay], FIND("-", 'YourTable'[WeekDay]) - 1))
VAR DayNum = VALUE(RIGHT('YourTable'[WeekDay], LEN('YourTable'[WeekDay]) - FIND("-", 'YourTable'[WeekDay])))
RETURN WeekNum * 10 + DayNum

This will make a new column that turns your values such as "103-1" to 1031 and "98-2" 982. From here, we will use this new column to sort your original WeekDay column.

In your table view tab, select your WeekDay column. In the top ribbon, click on the sort by column option and select the newly created column. This will sort your original column by the new column.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session

[rmcgrath]

I love the idea, but it throws an error:  

[SamsonTruong]

Hi @rmcgrath ,

It appears you be might be trying to do this as a measure. The code provided will only work as a calculated column.