Forum Discussion
Resolver ISorting visual by a "date" that is text based.
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
13 Replies
- jdbuchanan71
Super User
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.
PowerBINoob24Well 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)))).- 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".
rohit_singhSolution Sage
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
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
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.
- 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
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'
- PowerBINoob24
Okay...did that. Still doesn't change the sort order on the visual
- 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.
