How to disable sorting in Table visual

[Anonymous]

Hi,

I want to disable sorting on power BI table visual.By default table needs to be sorted based on first column.

How to achieve this?please help.

[JodyW]

I found a great way to do this. You just need to add an index column, sort by index column, and then make sure index column is hidden.

[MFelix]

Hi @JodyW ,

This will sort the column but won't solve the issue for the lock of the sort since if your user click on any other column of the table or matrix it will resort by that specific one, and then if the user wants to revert back to the previous sorting then they can't because they will not see the index column.

[JodyW]

Thank you. Yes I am finding this out now. I am doing an internship using Power BI in the final throes of my degree, so I am still learning, on my own. I had it working for one table but not the other. In fact for the one that worked I later discovered I had the index column still there but literally hidden from view, as in it went off the page on the right side. Don't know if there's a way to lock the sort. There is no reason for users of my report to be able to sort. But anyway I may be publishing as a pdf on a website, it's quite simply a monthly publication for the local council, river flow rates in order of importance, so my method may work for that. I haven't found the rectangle overlay method to work just yet. Anyway I have more important things to focus on for now.

[sseverson12]

Hi there,

To save future readers some pain, the other answers given here are not correct and will cause serious problems for you if you need to prevent users from sorting using the column headers. The issue is that even if you group the blank button and the table visual together, if a user clicks somewhere else on the table visual (e.g. to scroll or select a row) the table will become selected and the user will then be able to sort using the column headers.

Luckily, there is a solution if you put the visuals in separate groups. I found a great explanation here: https://www.youtube.com/watch?v=PKIcjGz_Swg

[MFelix]

Hi @sseverson12,

Since November 202 there is another option that you can turn on in each visualization to maintain layer order so the visualization will keep the order.

Check the explanation in the link below

https://www.havensconsulting.net/blog-and-media/using-the-maintain-layer-order-option-to-lock-in-vis...

The video is from the same user that you also shared a link. 

[Anand24 · Accepted solution]

Hi @Anonymous ,

@MFelixgave a wonderful work-around and that should probably suffice your requirement.

You just need to follow below steps:

1. Create a blank button or blank rectangle

2. Place it over the Table columns (Make sure you place the blank button/rectangle infront of table)

3. Group them together

I'm not sure if you are using table visual or matrix visual but anyway, the drlling options at top right of visual box will be visible to you and you should be able to drill.

With this work-around, you won't be able to perform any operations that are supposed to be related with clicking the table/matrix headers such as sorting in table/matrix and right click -> Drill in matrix.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[jfox0603]

Sad that sorting capabilities in Power BI is so clumsy and ineffective that we have to resort to these measures to simply lock a column's sort order from modification.

[Anonymous]

Hi,

Thanks for sharing this method. It works great for two of my matrix tables.  But it doesn't seem to work on my  third one, which has a lot of columns and I need to use horizontal scroll bar to go to the very end on the right.  

Do you have suggestion on how to have it work on a matrix report that has a lot of columns with horizontal bar enabled?

Thanks.

[MFelix]

Hi @Anonymous

The option is still the same only thing you need to garatee is that the shape fills all the wide of the table what I usually do is to create a shape a little large so if the table has 200 of wide I would do the shape with 205 for example. 

[Anonymous]

Thank you @MFelix,

It looks I have to cover the scroll bars for it to work. But I need to be able to scroll from left to right.

[MFelix]

Hi @Anonymous ,

You just need to cover the headers of the table no need to cover the scroll bar has you can see below I have left the border blue so that you could see the shape is until the scroll bar but not touching it and it's only on headers.

[Anonymous]

I know what is happening. I opened Selection pane. So if I directly click in the button covered area, in the selection, it shows I clicked on Group (Name) or Button. Then it is not impacting sorting. But as soon as I clicked on scroll bar, in the selection pane, it shows Matrix being selected and the sorting is then impacted. 

In order not to impact sorting, I would need to move my cursor to select either Group name or Button after I click on scroll bars. 

[MFelix]

HI @Anonymous ,

Forgot to mention one option that you need to have selected in the option that is maintain layer order.

Be aware that this workaround is for the service, on PBI desktop the workaround will not work because you are on editing mode.

[Anonymous]

Thanks @MFelix, will keep it on mind 🙂

[negi007]

@Anonymous  It may not be possible to disbale sorting option. I think as a workaround, you can draw a line on the top of the sort filter icon. This way sorting option will get hidden when user move cursor over the table which will indirectly disabled option to sort for the user

[MFelix]

Hi @Anonymous ,

This is not possible since the sorting option is based on the column headers and it's a default behaviour, what you can do has a workaround is to make your table sorting as you need then create a blank button with the size of your table headers and place it on top of your table visualization then just group both of them and user will not be abble to select the order:

[Anonymous]

Thanks for your response.I have applied drill through in that table so will it work once i group button and table that if right click on any row it will drill through to next page? and also i have multiple columns in table so i need to scroll the table.

[v-xuding-msft]

Hi @Anonymous ,

Does the answer of @MFelix  make sense? I think it is the best workaround for your requirement. If it is helpful, please accept it as solution. If you still need help, please feel free to ask us.

[MFelix]

Hi @Anonymous ,

I'm not sure about what answer you are refering to, but my answer of creating a button on top of the table will not disable the drilltrough and the scrolling since you should only place the button on top of the headers so to stop the users from clicking the header the scrolling is on the bottom and the drill trough should be made on a cell of the table so everything should work as expected.

[amitchandak]

@Anonymous , see if this can help

https://windowsreport.com/disable-sorting-power-bi/