sort multiple columns

[SumantShekhar]

HI,

I am rendring Table visual, Here I want to sort rows based on three columns, But currently sorting with multiple column is not available. So i tried to concatinate all three columns & created a calculated column for sorting order. But Again I dont want to make this calculated column to be visible in table visual, Instead i want to use this column for sorting. So i was looking for an option where i can hide this column hidden. This option is also not available.

Can anyone suggest me how can we implement sorting based on multiple columns with some workarround?

[v-yulgu-msft]

Hi @SumantShekhar,

As bidevsugmen suggested, you can use the "Sort By" feature. In Query Editor mode, add a custom column (suppose it's Col4) which combines Col1, Col2 and Col3. Based on Col4, please add a conditional column to list the sort order for each value in Col4. Then, in report view, click the Sory By option under Modeling tab in the top ribbon, change default selection to col4.

Best regards,

Yuliana Gu

[Ellie1979]

The order you have the columns will impact the sort on the table. Using the col1, col2, col 3 example a table with col1, col2, col3 will show differently to col3, col1, col2.

Alternatively, if you put your ranking/sorting caculated field at the start of the table and then use the 'Field Formatting' option you can set text and background to white (or a colour matching your report back groud) and apply to header, values and total this will effectively hide the column (you can also make this narrow in width by ensuring auto size column is off under column headers).

I am not sure sorting col1 (for example) on your calculated field as a value in col1 may have two different sort values due to col2 and col3 values and the sort option needs a one-to-one match (hope that makes sense)

[SumantShekhar]

I have tried this workarround, Looks good. But on hover on cells it feels like some information is missing, and tooltip renders the value of that cell. I am trying to hide columns by making width narrow but still hover reveles tooltip.

[Anonymous]

Power BI allows you to hide column in Report view. May be it can help you

[SumantShekhar]

I am trying to hide column from Table visual, This will hide column from field list only.

[Anonymous]

Hi Sumant,

Here is one of the workarounds -

Suppose the columns are col1, col2, col3 and your calculated column is col4.

Bring col1, col2, col3 in your table.
In the right hand side panel, click (select) on the field (either of col1, col2, col3) based on which sorting is to be done.

Go to Modelling tab in the top ribbon - Sort by column option - select the calculated column col4.

Hope this works for you!

[SumantShekhar]

I have tried creating col4 with my required Order, and from Modeling tabin top ribbon - Sort by column option selected col4. in Table visual its still not working. In Case i am keeping Col4 in my table visual & applying sorting from there its working. But as per my requirement i dont have to render col4 in table visual.