Sorting visual by a "date" that is text based.

[PowerBINoob24]

Hi all,

I have a line graph visual that shows data by month/year; however, the the data is a text field and the data is "11 2022 Name", "12 2022 Name", "01 2023 Name", "02 2023 Name", and so on.

On the visual when sorted they show up in the order of   ""01 2023 Name", "02 2023 Name", 11 2022 Name", "12 2022 Name", which is to be expected.

How I need it to sort is "11 2022 Name", "12 2022 Name", "01 2023 Name", "02 2023 Name", "03 2023 Name", "04 2023 Name" and so on.  Any thoughts on how to go about this?

TIA

[jdbuchanan71]

Can you share your file?  Put it on dropbox or onedrive and share the link here.

[PowerBINoob24]

I'll have to see if I can sanitize it.

[jdbuchanan71]

@PowerBINoob24 

You don't sort the table by the [test sort] column.   You set the sort order for the column you are showing in the x axis using [test sort] column.

In the table view.

Select the column you are showing in the x axis.

Go to the 'Sort by column' drop down and pick the [test sort] column.

You are telling PowerBI 'Sort this column I have clicked on using the values in this other column I pick in the drop down'

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto...

[PowerBINoob24]

Okay...did that.  Still doesn't change the sort order on the visual

[jdbuchanan71]

So you would need to add another column using the same source but change the output to be the numeric sort order for the field you already added.  What is the DAX code for the created column you are trying to sort?

[jdbuchanan71]

Sorry, I don't follow.  Do you mean the table that has the field you are trying to add the sort to is a calcualted table?  The code Text.Middle([Campaign Group],3,4) & Text.Start([Campaign Group],2)) will only work in PowerQuery, it will not work in DAX.  

If you are working on a calculated table you could add the sorting field at the same time as you caclulate the rest of the table.

[PowerBINoob24]

No.  It's not a calcuated table.  It's the original data table, but the column that I need to apply the formula to is not part of the original table, but was created by grouping together data from another column.

[rohit_singh]

Hi @PowerBINoob24 ,

In DAX, you can create a new column named "DateIndex" as shown below :

Then select the Date column, click on "Sort by column" and select DateIndex.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[jdbuchanan71]

@rohit_singh 

Don't you get a circular dependency from that?

[rohit_singh]

Hi @jdbuchanan71 ,

Thanks for pointing that out. I should have checked before posting. The Power query approach is the correct one. 
 
Kind regards,

Rohit

[jdbuchanan71]

You need a field that has the sort order in numeric format.  You can add one in PowerQuery by adding a custom column that gets the portion of the field you need to use for the sort.

Text.Middle([Text Field],3,4) & Text.Start([Text Field],2)

Then change the data type of the new column to whole number.

Then, in the table view change the sort by of the text field to use the new column.

[PowerBINoob24]

Okay.  So I managed to make some changes so I could implement what you suggested above.  Added a text sort column through Power query, went into the table view and the sorted by the test sort column.  It sorts as you stated it would in the table, but that doesn't impact the visual because the visual still sorts the x axis by the "01 2023 Name", "02 2023 Name", 11 2022 Name", "12 2022 Name".

[PowerBINoob24]

Well that would work, but I just realized that the Name field is a column created in a table that is a group, so when I go into power query it's not available as a column in the source spreadsheet if that makes sense.  I've tried this: 

Campaign Sort = Text.Middle([Campaign Group],3,4) & Text.Start([Campaign Group],2)) but that throws the following error: 

The syntax for '.' is incorrect. (DAX(Text.Middle([Campaign Group],3,4) & Text.Start([Campaign Group],2)))).