sort a table by another column in table but not in visual

[po]

Hi,

Have a table with calculated column h_sort  intention to allow sorting on this but not have column itself on table visual.

Tried sorting the table in data tab by this column and shikst shows in data in sorted order.

want sort data in degree of postcode match descending then day of week ascending i.e. similar to below.

How can this be best achieved in power bi without including h_sort in table visual itself as this table will be exported to excel sometimes and don't want this column visible or exported to excel.

Thanks

[amitchandak]

@po , Not sure I got it completely

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

[po]

Thanks for link but unfortunatley doesn't give us what we are after.

Have a column h_sort which  is calculated column which is how we wish  data in table sortde and have this in data itself.

We wish to sort the data in the table by this column but not show this column in the table itself.

Hope make sense.

Postcode Sector 0  (0 is Sunday)

Postcode Sector 1  (1 is Monday)

.....

Postcode Sector 6  (6 is Saturday)

Postcode District 0  (0 is Sunday)

Postcode District 1  (1 is Monday)

.....

Postcode District 6  (6 is Saturday)

Postcode Area 0  (0 is Sunday)

Postcode Area 1  (1 is Monday)

.....

Postcode Area 6  (6 is Saturday)

[v-easonf-msft · Accepted solution]

Hi , @po 

Here is a demo .  Pbix attached

If help ,try  following steps :

1.Create a calculate column "sort by colum"(Used to define your  sorting  rules for  "Day" column)

Sort by Column = SWITCH('Table'[Day],"SUN","0","MON","1","TUE","2","WEN","3","THU","4","FRI","5","SAT","6","FALSE")

2.It will report an error if you  click column "Day " and make it sort by "Sort by column"directly

so you need to create a new column”Day2“ and make it sort by "Sort  colum"

3. Replace field "Day2" with field "Day" to create a  table in report view

Click  column "Degree of Posctcode Match" to sort the data in the table

Then   "shift"  + click column "Day 2" to sort the data in the table

Result  will show as below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! You helped me solve a similar issue, too!!!

[po]

Hi,

Thanks for link and detailed explanation.

Never new abaout ability to hit shift to allow sortng on second column.

Thanks