Sorting text values

[Sunanda85]

Hi all,

I am having issues sorting the age group and tenure band values in ascending/descending values in a visualisation.

Is there way to do this. Some help with an example would be highly appreciated.

Thanks!

[AiolosZhao · Accepted solution]

Hi @Sunanda85 ，

You can try to create a mapping table manually, for example, in your fact table, the value of your age column:

1-10

10-20

20 +

then you need to create a table with 2 columns

age            index

20 +             1

10-20           2

1-10            3

merge it to your fact table to get index column

after apply, you can find a button named "Sort by Column" in Modeling tab(on the top)

select your age column firstly, then select the button, choose the index column.

I think that's what you want.

Please try.

Aiolos Zhao

[Sunanda85]

Thanks a lot Aiolos Zhao for this solution. Appreciate it. There is another issue i am facing after fixing this that if i have added any new calculated columns e.g. 'tenure band' or 'age band' using DAX, its not available in Query editor as its not part of the original source data. So to merge these queries with index tables; how to do i get these calculated columns displayed in query editor or do i have start from scratch and add them in query editor instead.

Appreciate your advise. Thanks in advance!

[AiolosZhao]

Hi @Sunanda85 ,

It's glad to help you. For me, I think it's better to create the "age class" in Power Query Editor,

like:

1.create a custom column

2.input below logic

if [age_column] >= 1 & [age_column] <= 10
then "1-10"
else if [age_column] > 10 & [age_column] <= 20
then "10-20"
.......

3.then use the merge function

Please try.

Aiolos Zhao