Custom Data Sorting

[johnce123]

Good Morning, 

Im trying to sort my data into a custom sort  (Acc sub category).  I have created a seperate table and taken the Unique values attributed to the Acc Sub cateogires and given them a sort order and connected the tables.

I however cant find the area where you tell it to use this connected table in the modelling section (like ive seen on examples) and only have the option to sort with current columns in the same table.  Help appreciate TIA

[TYOzzy]

I have literally spent days trying to get this column to sort chronologically.   I made a separate table with a month name and number, but it still doesn't sort correctly.  Please see the example.  I'm at my wits end here.  I've read so many posts and watch so many videos.  This looks like it should work to me, but it's not.  Absolutely any help would be greatly appreciated.  

Thanks in advance!

Tara

[v-rzhou-msft]

Hi @johnce123 

If you sort the Acc sub category column in your seperate table (Sort Table as below), this won't affect the Acc sub category column in your data table.

You may try to build a calculated sort column in Date table and sort the Acc sub category column in Date table.

My Data Table:

My Sort Table:

Relate two tables by Acc Sub Category columns.

Calculated column:

Acc Sub Sort = RELATED(Sort[Sort])

Sort Acc Sub Category column in Date Table by this Sort column and result is as below.

Or you can use Acc Sub Category column in Sort Table directly.

You can download the pbix file from this link: Custom Data Sorting

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[johnce123]

Many thanks for your reply. 

I am getting this info now, i have connected the two tables by the unique entry number for Acc Sub category

[v-rzhou-msft · Accepted solution]

Hi @johnce123 

I update my data models and connect two tables by this unique number column.

Data Table:

Sort Table:

Relationship:

Finally result is corret.

You can download the pbix file from this link: Custom Data Sorting

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@johnce123 , check what is sorted on the matrix under 3 dots. Can you share a sample pbix after removing sensitive data.

[johnce123]

To be honest im very new to this, only started learning last week.  How do i share the data Pbix without sensitive info (sorry)

[amitchandak]

@johnce123 , not very clear. In this new table, click on the Account sub category, You will see the sort option under column tools

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[johnce123]

I tried that but it hasnt done what im expecting and has kept it in alphabetical order