Forum Discussion

Sunanda85's avatar
Sunanda85
Frequent Visitor
7 years ago
Solved

Sorting text values

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 a...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Sunanda85
Frequent Visitor
7 years ago

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!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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