cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sunanda85 Frequent Visitor
Frequent Visitor

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 appreciated.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
AiolosZhao Member
Member

Re: Sorting text values

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

3 REPLIES 3
AiolosZhao Member
Member

Re: Sorting text values

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 Frequent Visitor
Frequent Visitor

Re: Sorting text values

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 Member
Member

Re: Sorting text values

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 341 members 3,912 guests
Please welcome our newest community members: